All Forums Database
08 Aug 2014
Delete from table all

What is the difference b/w below stmts ? When i compare expalin plan , both the cases its same.
delete from sales.table all
delete from sales.table
Really , both are same ? or any difference ?.
Q2 .  Noraml delete vs delete through MLOAD , which will work faster. ? how ?

nagendratrpth 25 posts Joined 03/14
08 Aug 2014

You can get Diff between delete & delete all into below page :
AS far as I know MLOAD works with data block  in place of row by row , and deleting with MLOAD would be faster then normal delete statement .

Raja_KT 1246 posts Joined 07/09
08 Aug 2014

For large number of rows, mload delete is faster. For deleting some rows, bteq can be faster.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
08 Aug 2014

Mantra: There's no difference between DELETE and DELETE ALL, ommmmm.
A full table SQL DELETE [ALL] will be faster than a MLoad DELETE, because MLoad actually scans the table.
If there's a WHERE condition a SQL DELETE might still be faster than MLoad especially if it based on the partitioning of the target table.
The main reason for MLoad DELETE was/is not runtime, it's system restarts: SQL DELETEs will rollback while MLoad DELETEs don't (can restart and finish)


09 Aug 2014

Thank u dieter..its really helpful .

You must sign in to leave a comment.