Re: About partitioning

2010/1/20 Adrian von Bidder <avbidder(at)fortytwo(dot)ch>:
> Hi,
>
> On Wednesday 20 January 2010 11.57:37 Vincenzo Romano wrote:
>> 2010/1/20 Adrian von Bidder <avbidder(at)fortytwo(dot)ch>:
>
>> > [ creating db partitions on demand ]
>> >
>> > On Wednesday 20 January 2010 11.20:21 Vincenzo Romano wrote:
>> >> In case 1 I need to inspect the catalog with at least a select, while
>> >> in case 2 I need to trap errors.
>> >> In my (little) experience trapping errors is slow, so I would go for
>> >>  option 1.
>> >
>> > Trapping/handling the error might be slow, but remember that creating a
>> > new partition (presumably) doesn't happen often (and creating the
>> > partition is slow anyway.)
>
>> Hmmm ... also trapping would happen for every single line being inserted
>
> Why?
>
> By "trapping" I mean: reacting to the error if the INSERT statement fails.
> If the INSERT does not fail, there is no error, so there is no error
> condition to handle.
>
> Compare:
>
>  * query server to see if partition exists
>   (!!! slow: this uses the database server)
>  * if partition does not exist (this is almost never the case), create it
>  * insert row
>
> Against:
>
>  * try inserting (same speed as the final step above)
>  * if (error)
>   (this is fast, since it only uses the return value from the insert.  No
> additional database action)
>   -> then create partition (this, again, is slow but almost never happens)
>   -> and then re-try the insert.

"trapping" should have the same meaning as in "38.6.5. Trapping Errors",
that is the BEGIN...EXCEPTION...END.
In my case:

BEGIN
INSERT INTO a_child_table SELECT NEW.*;
EXCEPTION
WHEN the_table_doesn_t_exist THEN
CREATE TABLE a_child_table ...
END;

"Tip: A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than
a block without one. Therefore, don’t use EXCEPTION without need."

So my fear is that having such a trapping block defined at runtime for
every INSERT
would yield to a slow implementation.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS