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

Hi,
You can get Diff between delete & delete all into below page :
https://forums.teradata.com/forum/database/delete-all-vs-delete
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 .
 
Thanks
Nagendra

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: http://en.wikipedia.org/wiki/User:Kt_raj1
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)

Dieter

09 Aug 2014

Thank u dieter..its really helpful .

You must sign in to leave a comment.