Re: Table Partitioning Advice Request

On Thu, Dec 17, 2009 at 8:29 AM, Vincenzo Romano
<vincenzo(dot)romano(at)notorand(dot)it> wrote:
> 2009/12/17 Sam Jas <samjas33(at)yahoo(dot)com>
>>
>> Rule is not advisable, Trigger is the best solution.
>
> Does the trigger solution need the TABLE CHECK constraint?
> It looks to me it won't.

The table check constraint is used during selects to pick just the
child tables needed to deliver the data you are asking for, assuming
the key value is in the where clause.

The main reason to avoid rules is that they're much less efficient
than triggers. We use partitioning at work for our stats db, and
partition by day, and we have about 2 years worth of stats data, so
our insert trigger has about 700 if / elseif / else conditions in it,
and the trigger is still very very fast. I tried it with rules before
and it was way too slow.

Note that the absolute fastest way to insert to the right child table
is to do it from the application side, choosing the right child table
there.

We automated our trigger creation. While the trigger is huge in terms
of number of lines, speed wise the creation of the new trigger each
night at midnight is measured in milliseconds