title: How to delete large amount of data from Oracle table in batches
body: <p>I'm using Oracle 10g and want to delete approx 5 million records from Persons table (Having total 15 million records) which doesn't have any reference in Order table (Having total 5 million records)</p>
  
  <p>Since deteting 5 million records in one go would cause undo log issues, I've decided to delete in batches of 100k. I'm using the below query:</p>
  
  <pre><code>DELETE FROM Persons p
        WHERE     City = 'ABC'
              AND NOT EXISTS
                     (SELECT O_Id
                        FROM Orders o
                       WHERE p.P_Id = o.P_Id)
              AND ROWNUM &lt;= 100000
  </code></pre>
  
  <p>Now the problem is that this query takes as long to execute for 100k records as it would have taken for 5 million because still full table scans and joins on both tables will happen.</p>
  
  <p>Is there a efficient way to rewrite this query for faster execution? 
  or replace NOT EXISTS clause with better join condition?
  or use some better way to limit records to 100k?</p>
  
  <p>P.S. This is a one time operation only and I can't use any DDL operations for this, however pl/sql is fine</p>