Load data to warehouse

[james_cc]

We are using Oracle Warehouse Builder product to load data into our warehouse. However, we have this huge package that takes 12 hours to load. I am just wondering, for a quick fix, we can replace some of the big tables with materialized view first, during the day, we can insert them into actual tables. Will this help speed up the process if so how much. The other obviouse thing is to index some of the tables. This package is very big, 120 page long. Any other suggestions.

[denevge]

'package is big' : it's a PL/SQL package

This means you only use 1 cpu, and you cannot use parallisme to speed up the procedure.

I would try to split it in 2 :
1.
a clean file to db load procedure -> using sql*ldr - direct load
2.
an etl procedure that processed the loaded data. Using partitioning on the staging-table it may be possible you can speed things up by executing the same procedures in parallel, just use another partition of the staging-table as your input for the procedure.

HTH
Gert