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

From my experience, the fastest way to delete lot of rows is :

solution 1 (recommended by Tom Kyte)

`SET TRANSACTION USE ROLLBACK SEGMENT <your_seg>
 DELETE FROM <tab1> WHERE <cond>
 COMMIT`

OR

solution 2

`create table new_table unrecoverable as select * from old_table where ....;
drop table old_table;
rename new_table to old_table;
create index old_table_idx1 on old_table(c1,c2) unrecoverable parallel 5;
`

I used the second solution in different contexts: it is always the fastest to delete huge amount of rows.

An alternative is to put the data to delete in a partition and then drop the partition (each partition has its own rollback segment, can use parallelism, ...).