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

Solution 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);

Solution 2

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, ...).

Solution 3

DECLARE
 v_limit PLS_INTEGER :=100000;

CURSOR person_deleted_cur
IS 
 SELECT rowid 
   FROM Persons p
  WHERE City = 'ABC'
   AND NOT EXISTS
               (SELECT O_Id
                  FROM Orders o
                 WHERE p.P_Id = o.P_Id);

TYPE person_deleted_nt IS TABLE OF person_deleted_cur%ROWTYPE
        INDEX BY PLS_INTEGER;
BEGIN
  OPEN person_deleted_cur;
    LOOP
      FETCH person_deleted_cur 
        BULK COLLECT INTO person_deleted_nt LIMIT v_limit;

    FORALL indx IN 1 .. person_deleted_nt.COUNT 
      DELETE FROM Persons WHERE rowid=person_deleted_nt(indx);

    EXIT WHEN person_deleted_cur%NOTFOUND;

   END LOOP;

   CLOSE person_deleted_cur;
  COMMIT;
END;
/