Re: Performance on Bulk Insert to Partitioned Table

Hi,

On 21 December 2012 04:29, Charles Gomes <charlesrg@outlook.com> wrote:
> When I target the MASTER table on all the inserts and let
> the trigger decide what partition to choose from it takes 4 hours.
>
> If I target the partitioned table directly during the
> insert I can get 4 times better performance. It takes 1 hour.

Yes, that's my experience as well. Triggers are the slowest.
Performance of "DO INSTEAD" rule is close to direct inserts but rule
setup is complex (each partition needs one):

 create or replace rule <master_table>_insert_<partition_name> as on
insert to <master_table>
                        where new.<part_column> >= ... and
new.<part_column> < ....
                        do instead
                        insert into <master_table>_<partition_name>
values (new.*)

The best is used to direct inserts (into partition) if you can.

--
Ondrej Ivanic
(http://www.linkedin.com/in/ondrejivanic)