SQL delete query optimization

Question

DELETE edms_personalisation 
WHERE  mail_id NOT IN (SELECT mail_id 
                       FROM   edms_mail) 
       AND rownum <= 25000; 

Hello, can the SQL (run from a SQLPLUS interface) above be written in a more efficient way?

I was recently alerted to the fact that the edms_personalisation table has approx 80 Million rows and the edms_mail table has approx 120,000 rows.

I want to create a clear down script that only affect a limited number of rows per run so I can stick it on a Cron Job and get it to run every 5 minutes or so.

Any ideas welcome.

(Note: Appropriate indexes do EXIST on the DB tables)

Answer 1

DELETE edms_personalisation p 
WHERE  NOT EXISTS (SELECT 'X' 
                   FROM   edms_mail m 
                   WHERE  m.mail_id = p.mail_id) 
       AND rownum <= 25000; 

or

DELETE edms_personalisation 
WHERE  mail_id IN (SELECT mail_id FROM edms_personalisation 
                   MINUS 
                   SELECT mail_id FROM edms_mail) 
       AND rownum <= 25000; 

If Oracle I would have written a PL/SQL to bulk collect all the qualifying mail ids to be deleted.And make a FORALL DELETE querying the index directly(Bulk Binding). You can do it in batch too.

Otherwise since the 'to be deleted' table is too big, wiser to copy the good data into temp table, truncate the table, and reload it from temp. When it has to be done in a frequent cycle, the above methods have to be used!

Try this! Good Luck!