Jump to content

sundara

Members
  • Content count

    1
  • Joined

  • Last visited

Community Reputation

0 Neutral

About sundara

  • Rank
    Newbie
  1. sundara

    Huge SQL data Insert

    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;
×