All Forums Database
foxbat 27 posts Joined 06/07
12 Oct 2007
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?

rwenzlofsky 4 posts Joined 08/04
12 Oct 2007

As far as I know there is no difference.Best RegardsRoland

Jagdish 14 posts Joined 08/07
16 Oct 2007

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

26 Dec 2007

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!

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

dnoeth 4628 posts Joined 11/04
27 Dec 2007

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 multistatementANSI mode:delete from tab; COMMIT; -- as multistatementDieter

Dieter

01 Jan 2008

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!

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

Luckyhansh 30 posts Joined 08/06
09 Jan 2008

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)LuckyhanTeradata Certified Prof

Fred 1096 posts Joined 08/04
10 Jan 2008

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.

15 Jan 2008

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

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

dnoeth 4628 posts Joined 11/04
15 Jan 2008

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

Dieter

You must sign in to leave a comment.