RE: Paralellizing Pl/sql inserts

Yes, I have received many good suggestions and I am benchmarking them now. Here are the results so far.  

The big table has 14 partitions on sales date. Each partition corresponds to a calendar quarter (2005q1, 2005q2, 2005q3 etc.) Primary partition refers to the partition that will receive 80-85% of the inserts. Unfortunately the other 15% of data hits all the other partitions.  

Case 1 - Use bulk inserts with save exception clause - PK + 5 indexes enabled - 1,000,000 test - 15 minutes

Case 2 - Merge when not matched insert (no parallel) - PK + 5 indexes enabled - 1,000,000 test - 11 minutes

Case 3 - Merge when not matched insert (no parallel) - PK + 5 indexes enabled - 2,000,000 test - 11 minutes  

Planned Cases

Case 4 - Merge when not matched insert (parallel 16) - PK + 5 indexes enabled - 1,000,000 test

Case 5 - Merge when not matched insert (parallel 16) - PK enabled and 5 indexes disabled on primary partition- 1,000,000 test

Case 6 - Use bulk inserts with save exception clause - PK enabled and 5 indexes disabled on primary partition - 1,000,000 test

Case 7 - Use an insert as select with a not exists - TBD  

Thanks,

Ken      

From: Finn Jorgensen [mailto:finn.oracledba_at_gmail.com] Sent: Wednesday, January 23, 2008 9:23 AM To: ukja.dion_at_gmail.com
Cc: Ken Naim; oracle-l_at_freelists.org
Subject: Re: Paralellizing Pl/sql inserts  

Lots of good suggestions here.  

How is your big table partitioned? If it's something like 128 hash partitions it's going to be a pain, but if it's something more manageable, you could try splitting your 65 million row inserts into data by partition and do insert /*+ append */ bigtable (partition). Each of those can go in parallel (seperate stmts). Drop indexes first and recreate using massive parallel hint after inserts are done.  

Let us know how it works out for you. It's an interesting problem.  

Finn  

--
http://www.freelists.org/webpage/oracle-l