Hi,
We have a table with millions of records (approx 120-125 M) and we have to purge few old records which will be close to 35 M (Table is not date partitioned)
It's a live production system. Ofcource we can't simply issue single delete statement and delete all 35 M records in one go.
We plan to create a pl/sql unit to delete records in Batch (lets says of size 100k)
Now the question is, out of two below available methods which will be best from performance point of view.
Background:
Tables:
PURGE_TABLE (table to be purged, primary key REF_NO)
DELETE_ID - Column1: REF_NO (list of REF_NO to be deleted), Column2: BATCH_NO
Option 1:
For I IN 1.. TOTAL_BATCH
LOOP
DELETE FROM PURGE_TABLE WHERE REF_NO IN (SELECT REF_NO FROM DELETE_ID WHERE BATCH_NO= I)
COMMIT;
END LOOP;
Option 2:
For I IN 1.. TOTAL_BATCH
SELECT REF_NO INTO TYPE_VAR FROM DELETE_ID WHERE BATCH_NO= I;
FORALL J IN TYPE_VAR.FIRST..TYPE_VAR.LAST
DELETE FROM PURGE_TABLE WHERE REF_NO = TYPE_VAR(J);
END LOOP;
What will be the best approach?
Regards!