CODEWITHSUNDEEP
sqlquery-optimizationsqlplussql-delete
MarkP
MarkP

Reputation: 481

SQL delete query optimization

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)

Upvotes: 3

Views: 2411

Answers (3)

Punter015
Punter015

Reputation: 1796

  1. I think the delete statement in the question will work just fine. The question is how much amount of redo log will the delete statement generate.

  2. General rule of thumb would be to delete rows batch wise with a commit in it although batch size shold not burst out the online redo log files. [i suppose the question is related to ORACLE]

  3. If the delete is once in a time activity but you are doing it every 5 minutes with a batch of 25000 to cope up with the amount of rows to be deleted then copy out the required rows on to a new table, truncate the actual table and transfer data from new table to actual table. Of course doing it every five minutes would not make sense according to me.

  4. If the data to be deleted will be huge for the first run but not for the subsequent runs then i would suggest to follow the method mentioned in 2nd point for the first run and the method mentioned in 1st point for the subsequent runs.

DISCLAIMER: I think others would have faced the same problem and would have solved it with a better solution then mentioned above.

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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!

Upvotes: 4

Shavkat
Shavkat

Reputation: 83

You can SET ROWCONT N to limit the rows affected by next batch to N. Eg.

SET ROWCOUNT 10
GO

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

Will delete not more than 10 rows.

Otherwise, I would think about using bulk copy (or bcp) to copy the data to a new table, skipping the rows you planned to delete. FYI:

  • This will work if the table doesn't change often, or you pre-create INSERT/UPDATE/DELETE triggers to replay all changes made to the source table during the copying process.
  • If you use bcp, than the out/queryout will not block the source table, but in will block the destination table.
  • You should pre-create all indexes on the destination table with proper fill factor, or otherwise you will have to create them on a table with a lot of data after you are done copying, and this will take much time.

Upvotes: 0

Related Questions