Jump to content

Search the Community

Showing results for tags 'rownum'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type



Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests

Found 1 result

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