Delete from table all

Database
Enthusiast

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 ?

4 REPLIES
Enthusiast

Re: Delete from table all

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

Enthusiast

Re: Delete from table all

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

Junior Contributor

Re: Delete from table all

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)

Enthusiast

Re: Delete from table all

Thank u dieter..its really helpful .