Extracting large data volumes to flat files - Performance
We are working on a data mart and we need to extract large
volumes of transactional data from the source system (also
Oracle DB) to flat files. We then use SQL Loader with the
direct option to load the data into our staging tables. While
the Load performs very well (less than 10 minutes to load 4
million records), the extract is Extremely slow. The method we
are currently using for the extract is an sql script (SQL Plus)
spooling to a comma separated flat file. For 4 million records,
the process is taking 5 hours to complete! We moved to a dual
processor machine and that didn't make any difference - in fact,
both CPU remain over 90% idle while the extract is running. Is
there a more effective than SQL Plus to extact data to a flat
file?