delete and delete all

Tools
Highlighted
Enthusiast

delete and delete all

hi friends,

what are the differences between delete and delete all?
3 REPLIES
Teradata Employee

Re: delete and delete all

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

Re: delete and delete all

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.

Enthusiast

Re: delete and delete all

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.