parallel partitioned direct-path insert

I'm looking for the fastest way to get a massive amount of data into a partitioned table in a data warehouse. The table structures are different and there will be some simple validation during the load.

First, for a parallel direct-path insert on a partitioned table, Does Oracle have one producer query that directs each resulting row to the correct parallel insert process for the partition it is handling? Or, does each parallel insert process issue it's own consumer query (maybe adding it's partition range to the original query)? Or something else?

If there is only one query, won't this produce essentially serial results if the data is in order by partition. In other words, won't it send all the first set of rows to the first parallel insert process, then switch to the second process, etc... Presumably, only one partition would be receiving inserts at one time.

The alternative, as I see it, is to build each partition manually as a table and then exchange them in. I could build each table in parallel with a direct-path insert (insert /*+append*/).

Also, there is a possibility that the disk volumes may be striped across the entire disk array. Will this effectively negate the benefit of parallel inserts? It would seem that the process will become i/o bound on the inserts. At which point, all the disks should theoretically be going at maximum throughput. So, additional inserts would just be waiting as well.

Thoughts? Suggestions?

Thanks,
Richard Received on Mon May 06 2002 - 17:37:49 CDT