Optimizing Oracle Inserts with FORALL

I was recently faced with a daunting task of processing approximately 300,000 records for an insert into a denormalized table in an Oracle 10g database for historical reporting. The table that was the starting point had no records that had been modified, however all of the tables the table was related to had many modifications on all records. The project required me to go through every record in the starting table and then reconstruct all of the related rows to their historical value at the time the base records were inserted.

My initial go around at the task involved writing stored procedures that retrieved historical values for a given primary key and date value.  By digging through the historical values from audit tables, I was able to determine the value for the given row at the given date.

After writing the necessary stored procedures to grab historical values from the related tables, I wrote a PL/SQL block to loop through all of the base records and then grab the historical values into rowtype variables and performed the inserts. The initial run of the script worked perfectly however it took approximately 7 hours and 15 minutes to run averaging about 11.5 records per second.

While this run time was acceptable to the requirements, I knew there had to be a better way to accomplish the task with a faster run time. Knowing the benefits of Oracle's forall, I converted all of my PL/SQL stored procedures that retrieved historical values to views so I could boil the inserts down to one SQL statement. Then I converted my PL/SQL for loop to a forall with the one insert statement.

My new and improved script ran in 6 hours even and averaged 13.9 records per second, a saving of 20%. The next time you are looking to cut some time from a time consuming data insert, try out the forall query. You can learn more about forall at Oracle's website.