Performance between Delete In and For Loop 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 &lt; 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 &lt; trunc(sysdate))


Is there any performances issue? which one is a better way to delete?
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 &lt;new table&gt; as select * from &lt;old table&gt; where &lt;condition&gt;, 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.