Q

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.


This was first published in August 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close