All Forums Tools
teramurali 21 posts Joined 11/08
24 Mar 2009
delete and delete all

hi friends, what are the differences between delete and delete all?

Fred 583 posts Joined 08/04
24 Mar 2009

No difference, other than the noise word "ALL".

naren 4 posts Joined 09/06
18 Jun 2009

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.

Jimm 298 posts Joined 09/07
18 Jun 2009

Fred is right! "Delete All" requires you to type 3 extra characters - that is the only difference.Under certain circumstances, Delete will use a Fastpath which avoids taking before images to the transient journal. Summary of the circumstances is:1. The statement is not running as part of an explicit or implicit transaction. So you are in Teradata mode, not running in a macro, no BT/ ET around the code. It will use Fastpath if the Delete is the last statement in a transaction (or the last statement in a multistatement request). 2. A "Where" clause will usually mean that Fastpath cannot be used. Exception is where the Where clause specifies the partitioning key on a table with a PPI. In this case Fastpath will be used.See Delete in the SQL ref manual for more info, although it is less than clear about use of the keyword all and it is easy to see why people get confused about this so often.

You must sign in to leave a comment.