DELETE ALL vs DELETE

Database
N/A

DELETE ALL vs DELETE

A few TERADATA users in the community recommend use of the

DELETE FROM ALL; /*Teradata extension to ANSI SQL 2003*/

syntax over
DELETE FROM
; /*ANSI syntax*/

I am under the impression that both statements work the same internally as
I haven't found any supporting TERADATA documentation which says these
statements work differently.

Can someone tell me if they know the reason behind this recommendation?

9 REPLIES

Re: DELETE ALL vs DELETE

As far as I know there is no difference.

Best Regards

Roland

Re: DELETE ALL vs DELETE

Hi dear,

There is no difference between DELETE ALL and DELETE in performance.
Usually basic DELETE is preferrable than DELETE ALL since basic DELETE is ANSI standard.

The only case it is advisable to use DELETE ALL is in BT/ET mode.
It is recommended to use DELETE ALL statement as the last statement in the BT/ET for faster usage of cpu time.

i.e.
DELETE ALL
;END TRANSACTION;

and dont be in a confusion DELETE ALL is recommended over basic DELETE.

hav a nice time.

thanks

Teradata Employee

Re: DELETE ALL vs DELETE

There is a difference in performance between DELETE and DELETE using the ALL keyword.

Using DELETE on its own causes reference to the transient journal.

Using DELETE with the ALL keyword bypasses any reference to the transient journal, which will definitely impact system performance.

Personally, I do not work in a production environment so have no need of backing up any data I am deleting, so I always use the DELETE ALL syntax.

Hope this helps!
N/A

Re: DELETE ALL vs DELETE

There's definitely *no* difference between DELETE and DELETE ALL.

A FastPath Delete without transient journal just requires:
- a target table without Permanent Journal, Delete Trigger, Join Index
- the delete must be the last statement within a transaction and this must be known to the optimizer.

BTET mode, implicit transaction:
delete from tab;

BTET mode, explicit transaction:
BT;
...
delete from tab;ET; -- as multistatement

ANSI mode:
delete from tab; COMMIT; -- as multistatement

Dieter
Teradata Employee

Re: DELETE ALL vs DELETE

Thanks for that Dieter.

In our training course we present DELETE v DELETE ALL in the way I have described it. This obviously is incorrect. Can you think where we will have gotten this from? Did there used to be a difference? Is there a difference between BTET and SQL Assistant and DELETE v DELETE ALL?

Many thanks!

Re: DELETE ALL vs DELETE

There is difference between Delete and Delete all,
Delete all will truncate the index table,
while delete will maintain the index table,
also i thought their should be difference in journals,
in fact, you can use explain to see what happened during delete and delete all( i have no environment to run it now)

Luckyhan
Teradata Certified Prof
Teradata Employee

Re: DELETE ALL vs DELETE

I believe Dieter is correct. The only difference is syntax - choosing to explicitly state ALL rather than just omitting the WHERE clause. There is no difference in semantics or database behavior.

If anyone has proof to the contrary, I'd like to see it. In every case I've ever seen, the real difference actually turned out to be something other than the ALL keyword.
Teradata Employee

Re: DELETE ALL vs DELETE

Howdy folks,

As a trainer, I need to ensure that our material is kept up to date.

I e-mailed Teradata directly, and can confirm that DELETE and DELETE ALL are equivalents. There was a difference in previous versions, but no longer.

If you run an EXPLAIN on both, there is no difference to the output. Also, I populated two tables with 1.5m rows and tested it, and saw no difference in performance.

Thanks!!!

Andrew
N/A

Re: DELETE ALL vs DELETE

Hi Andrew,
which training material you're referring to?
It's covered correctly within the "Teradata SQL" and the "Teradata Application Design and Development" training.

And those "previous" versions must be V1Rx :-)

Dieter