How to delete large amount of data from Oracle table in batches

Question

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)

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:

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 <= 100000

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.

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.S. This is a one time operation only and I can't use any DDL operations for this, however pl/sql is fine

Answer 1

If you want this query to run faster, add the following two indexes:

 create index idx_persons_city_pid on persons(city, p_id);
 create index idx_orders_pid on orders(p_id);