Jump to content
sundara

Huge SQL data Insert

Recommended Posts

 

I have couple of tables that I need to use to load a 3rd table. The total number of rows to load comes around 30 million. My Insert SQL query is a INSERT-INTO-<TABLE>-SELECT.

My Insert SQL query hangs (Waited for 30 mins before killing the session). But when I used ROWNUM in the SELECT query with the ROWNUM value equal to the total number of result rows, the INSERT query ran in 43 seconds.

And, both the results are consistent.

I am completely lost here. Can you please throw your expert opinion on why the INSERT query without ROWNUM hangs and the INSERT query with ROWNUM runs much faster ?

Details :

TABLE - A : has a total of 50,000,000  rows; No index

TABLE - B : has a total of 200,000 rows; Index present in COLUMN1 (Relation with TABLE-A is one to maasny)

TABLE - C : Output table

 

Original INSERT query:

INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B);

INSERT query with ROWNUM :

SELECT COUNT(*) FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B); -- No issues with this query

INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B) AND ROWNUM <=30,000,000;

 

Share this post


Link to post
Share on other sites

Hi Sundara, and welcome to the forum!

The only way to understand the performance difference to to capture the execution plan.  See here, and use auto trace:

http://www.dba-oracle.com/plsql/t_plsql_plans.htm

Are the table statistics up to date?  It’s possible that the ROWNUM is hinting Oracle about the cardinality of the table.

As for tuning inserts, I have some good notes here:

http://www.dba-oracle.com/t_insert_tuning.htm

Hope this helps . . .

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×