Transient journal records support rollback, but their size and form depend on the operation and optimizations. Do not assume equal space or a full before-image for every affected row; physical journal storage was not measured here.
Part of the Teradata Internals and Operations guide.
Users do not have access to the transient journal, which is located in the DBC database. Ensuring sufficient permanent space in the DBC database is essential to prevent system-level errors.
Table rows and their associated transient journal rows are consistently located on the same AMP. The distribution of these rows follows the customary hashing algorithm.
The transient journal facilitates the rollback of changes in the event of an error or manual transaction abortion. It writes altered rows back to the base table, ensuring data integrity. Such a feature is indispensable in a relational database system like Teradata.
The utilization of the transient journal has a negative impact on performance. Teradata, however, employs techniques to mitigate journaling and circumvent the row-by-row logging mechanism.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookAllow recovery to complete under a documented DBA procedure. Forced rollback cancellation can invalidate a table and is not a routine way to recover performance.
Teradata provides methods to circumvent the transient journal or store only a single row in it.
There are situations where a row-by-row rollback is unnecessary. Here, I will present techniques that can enhance efficiency and prevent problems.
INSERT into an empty table.
The separate INSERT requests below can have different journaling costs as the target changes from empty to populated. The first request is eligible for an empty-target optimization only when all documented prerequisites are satisfied; the syntax alone does not establish its journal behavior.
INSERT INTO target SELECT col FROM table1;
INSERT INTO target SELECT col FROM table2;
An empty-target INSERT SELECT may qualify for fast-path processing under documented restrictions. Neither UNION ALL nor BTEQ request grouping alone guarantees no row-level journaling. Verify table state, eligibility and recovery behavior.
The table-copy/DROP/RENAME excerpt is an incomplete recovery-sensitive example: replacement changes object identity, grants and dependencies. Plan concurrent access, transaction boundaries and recovery before applying it.
INSERT INTO target SELECT col FROM table1 UNION ALL SELECT col FROM table2;
Replace UPDATE with INSERT INTO … SELECT into a copy of the target table
It is advisable to refrain from using UPDATE statements on heavily skewed tables because they are not optimized for Teradata’s parallel architecture and tend to have poor performance.
UPDATE target SET col2 = 1; -- assumption: col2 is skewed
INSERT INTO copy_of_target SELECT col1, 1 AS col2 FROM target;
DROP TABLE target;
RENAME TABLE copy_of_target TO target;
A BTEQ trick
INSERT INTO target SELECT * FROM table1 -- The semicolon starts on the following line!
; INSERT INTO target SELECT * FROM table2; -- Final semicolon terminates this BTEQ request.
Load Utilities and the Teradata Transient Journal
Bulk load utilities can be effective for large inputs, but relative throughput depends on target restrictions, batching, concurrency and recovery requirements. Compare complete workloads instead of assuming every bulk load outperforms a transactional load.
Validation scope: Explicit BT/ET failure rolled back the preceding DELETE in the isolated fixture; separate implicit requests left the committed DELETE in place. Physical journal records and interruption of recovery were not tested. Fast-path empty-target exceptions require their own eligibility checks.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
HO To update a huge table means out of 10 billion records i want to 1 milion records?
Valuable update on TJ….
Can we manually purge the TJ, there is no/less activity but it is consuming more than 2.5TB of space
Regards
Ashok
Hi Roland,
I suspect that a MERGE statement is also lower in TJ or at least more efficient. The orange book seems to hint at that, but only compares multi-load apply and ANSI merge.
Regards, Johannes