Problem solve Get help with specific problems with your technologies, process and projects.

PL/SQL procedures for data extraction taking too long

We have an Oracle 8 database for one of the biggest telecom providers. Now on daily basis we extract a huge amount of data from the main database server to another database. But our main problem is that it takes lots of time (approximately 16 hours) for extraction and insertion. We are using PL/SQL procedures, which are scheduled to run at midnight and be completed in 16 hours. Is there some better solution for this problem? We need to make the data available within six or seven hours.

PL/SQL can be rather slow for bulk inserts of data. I would look into using SQL*Loader, External Tables (if using Oracle 9i+), or straight SQL statements. I have a white paper on my Web site titled "Data Loading with External Tables," which discusses a sample performance case loading data in multiple ways.

SQL*Loader is Oracle's utility for bulk loading data from a text file. So you'll have to dump the data to a text file first. But the speed of this utility can't be beat.

You are not using Oracle 9i, so the External Table option is not available to you. External Tables use the SQL*Loader engine to load the data, but there are nice features to this option. The white paper mentioned above discusses External Tables.

A simple SQL statement, if possible, would be preferrable to any PL/SQL block to load data. Such an SQL statement might look similar to the following:

 INSERT INTO destination_table
 SELECT * FROM source_table@source_database
 WHERE source_column=my_condition;
In this case, the source table is in a remote database, accessed through a database link.

If you convert to SQL or SQL*Loader, you should see improvements in your loading times. And these improvements can be quite significant.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.