RE: Paralellizing Pl/sql inserts

Sorry had a copy and paste error.

 

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 - 20 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@xxxxxxxxx] 
Sent: Wednesday, January 23, 2008 9:23 AM
To: ukja.dion@xxxxxxxxx
Cc: Ken Naim; oracle-l@xxxxxxxxxxxxx
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

 

Other related posts: