Hi,
I need some help deleting batches from a really large online transactions table (up to 1 billion records). I hope to delete around 9 million records daily. 9 million more are being added daily. I have an off-peak time window when customer usage is limited so I can try to run this optimally, but I'm also conscious of not impacting any potential customers too much by specify too high a batch size below(10,000). Its Oracle 12.2 Standard Edition so unfortunately partitioning is not an option. I've come up with the following but its just not deleting fast enough. The initial select seems to be ok, its more about my loop. Is there a more efficient way of batching this?
DECLARE
cursor cur is
select /*+ index_ffs(a,P_ITD) parallel_index(a,P_ITD,4) */ C_ID from ITD a WHERE CREATED < '27-NOV-20 12.00.00.000000 AM';
TYPE CII_TYPE IS TABLE OF NUMBER;
CII_TYPE_TBL CII_TYPE;
BEGIN
OPEN CUR;
LOOP
FETCH CUR BULK COLLECT INTO CII_TYPE_TBL LIMIT 10000;
FORALL i IN 1..CII_TYPE_TBL.COUNT
DELETE FROM ITD WHERE C_ID=CII_TYPE_TBL(i);
COMMIT;
EXIT WHEN CUR%NOTFOUND;
END LOOP;
CLOSE CUR;
END;
/
P_ITD is the primary key constraint on the ITD table on C_ID
CREATED_ON is also indexed separately.
Thanks