title: Oracle insert huge amount of data via SQL
body: <p>I have couple of ORACLE tables that I need to use to load a 3rd table. The total number of rows to load comes around 30 million. My <code>Insert</code> SQL query is a <code>INSERT-INTO-&lt;TABLE&gt;-SELECT</code>. </p>
  
  <p>My query hangs (waited for 30 mins before killing the session). But when I used <code>ROWNUM</code> in the <code>SELECT</code> query with the <code>ROWNUM</code> value equal to the total number of result rows, the <code>INSERT</code> query ran in 43 seconds.</p>
  
  <p>And, both the results are consistent. </p>
  
  <p>I am completely lost here. Can anyone please throw their expert opinion on why the INSERT query without ROWNUM hangs and the INSERT query with ROWNUM runs much faster ? </p>
  
  <p><strong>Details :</strong></p>
  
  <ul>
  <li>TABLE - A : has a total of 50,000,000  rows; Primary key on column1 and 12 other indexes including one on column2</li>
  <li>TABLE - B : has a total of 200,000 rows; Index present in COLUMN1 (Relation with TABLE-A is one to many)</li>
  <li>TABLE - C : Output table</li>
  </ul>
  
  <p><strong>Original INSERT query:</strong></p>
  
  <pre><code>INSERT INTO C 
  SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B);
  
  Data Types involved in the above query are NUMBER.
  </code></pre>
  
  <p><strong>INSERT query with ROWNUM :</strong></p>
  
  <pre><code>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 &lt;=30,000,000; -- result from the above select query
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/IsB9Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/IsB9Z.png" alt="Explain Plan of the query with rownum"></a></p>
  
  <p><a href="https://i.stack.imgur.com/q1oFV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q1oFV.png" alt="Explain Plan of the query without rownum"></a></p>