1

I'm trying to find out what is the most efficient way for a procedure to delete hundreds million of records

    For B in (select id from invoice where data < trunc(sysdate)) LOOP
      delete invoice_details where details_id = b.id
    END LOOP;

vs

    delete invoice_details where details_id in (select id from invoice where data < trunc(sysdate))

Is there any performances issue? which one is a better way to delete?

  • JSapotka already explained that the last is the more performant way. But I think it makes sense if you supply more context. There may be better methods. how large is your table, what type of environment(is a downtime possible, your hardware resources) , what Oracle version and edition,... If you useyour statements you will need a lot of space for the undo information. Also it may be necessary to reoganize your tables after the deletion. – miracle173 Mar 7 '17 at 11:40
  • This is something you need to do by yourself since no one else has your setup ready. Use this utility to measure and decide what works best for you. – Raj Mar 7 '17 at 12:21
2

If the task can be performed using native SQL then always go for it. Because SQL and PLSQl are two different languages and if you call SQL from PLSQL block then context switch(exchange of processing control between SQL and PLSQL) occurs.

So I would prefer the native SQL.

Now, you have couple of options such as-

If you are going to delete large percentage of rows from the table, keeping 1 million rows is better that deleting 2 millions rows from table having 3 millions rows,

Then create <new table> as select * from <old table> where <condition>, you can also use parallel here, drop old table, rename to , create necessary indexes in parallel.

Down side of this method is that other session can't use this table during the operation.

Another method is to partition the table then drop the unnecessary partitions.

Note: If you still want to do it with PLSQl(for concurrency) then instead of deleting one by one, you can use BULK DELETE.

  • i've tested, native SQL is faster in this case, the contrast different is 1 hours vs 15mins for 4 million rows – zhenguang Mar 24 '17 at 3:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.