Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?

Database
Enthusiast

Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?

Hi,

There is popular opinion among the TERADATA user community that "DELETE ....ALL" doesnt use the Transient Journal. Is this true?

I tried testing this with an explicit transaction (within a BT-ET) and a rollback soon after a "DELETE....ALL" gets the data back.

How does it work for a implicit transaction? Is the Transient Journal updated with before images for the "DELETE .....ALL" operation?

thanks,
Foxbat

6 REPLIES
Junior Contributor

Re: Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?

It's not an "opinion", it's the truth, but you don't need the ALL :-)

Right now i don't want to explain in detail, just search the manuals for "fast path delete"

Dieter
Highlighted
Teradata Employee

Re: Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?

The ALL keyword is unimportant. You can get fast path delete behavior without ALL; conversely using ALL does not guarantee fast path.

Fast path delete requires (among other restrictions) that the DELETE be the last/only statement in the transaction, so that Teradata knows a ROLLBACK will never be required. And it will not only omit journaling, it can just mark blocks/cylinders "free" and not process individual rows at all.
rgs
Enthusiast

Re: Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?

Delete ALL uses the “fast path” for the following situations

• when it is the only statement in the transaction
• when it is the last statement in the transaction

This means if you run in Teradata session mode it has to either be an implicit transaction or the last statement in the transaction. It has to see the delete statement and the ET in the same request.

If you run in ANSI transaction mode it has to be submitted with the commit statement as one request otherwise it will not use the fast path.

The fast path still records the delete all request to the TJ, but only one row, to tell it if the transaction commits to delete the contents of the entire table, simply freeing all the data blocks in the table. If the transaction rolls back the TJ row will be ignored and the delete of the table contents will not be done.
Enthusiast

Re: Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?



I have seen the term fast path delete in the V2R6.1 SQL Reference for DML's but it doesnt explicitly say anything about the Transient Journal activity. I therefore didnt want to make a personal interpretation of the text. If there is some place this is specified explicity kindly let me know.

Also I observed that a DROP TABLE works faster than a DELETE ALL statment on a very large table. The DROP TABLE ran in a few seconds while the DELETE took a few minutes. I confirmed that the delay wasnt due to BLOCKING or TDWM rules. Any idea why this could have happened?
Enthusiast

Re: Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?

Hi Dieter/Fred,

Just want to clear my doubt that if DELETE statement is the last/Only statement whether its a ANSI or  TD mode , it will always Omit the TJ ..

Am I Correct here? Please correct me if i am wrong..

Cheers!

Nishant 

Junior Contributor

Re: Is the popular opinion that DELETE ALL doesnt use the Transient Journal true?

Hi Nishant,

it's a FastPath Delete when there's no Trigger/Foreign Key/Join Index and it's known to the optimizer that it will be commited. i.e. the explain must show the END TRANSACTION step.

For ANSI sessions using a multistatement request:

DELETE FROM tab
;COMMIT;

For explicit transaction in Teradata sessions using a multistatement request:

BT;
...
DELETE FROM tab
;ET; --multistatement request

without BT it's a simple

DELETE FROM tab;

Dieter