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 ?
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 .
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)