|
|
|
Junior Member
 
Group: Forum Members
Last Login: 10/20/2009 2:16:10 AM
Posts: 21,
Visits: 72
|
|
hi friends,
what are the differences between delete and delete all?
MURALI
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 9/9/2009 4:34:06 PM
Posts: 407,
Visits: 889
|
|
|
No difference, other than the noise word "ALL".
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/22/2009 10:35:50 AM
Posts: 4,
Visits: 21
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
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.
|
|
|
|