Improving performance by avoiding transaction logging on Load Isolated tables in Teradata

In Teradata database, the transient journal (TJ) is a system-maintained log that provides a way to protect transactions from various system failures. Each transaction processed by the database records a before image of rows that are touched by the transaction. Then if a transaction fails for some reason, the before image of any modified rows can be retrieved from the transient journal and written over the modifications, returning the row to the same state it was in, before the transaction started. Teradata Database removes all before images from the transient journal after a transaction commits [1]. In addition, Teradata provides Load Isolation feature, a table isolation property (MVCC variant), that enables concurrent read of committed rows while the tables are being modified. To enable committed reads in concurrent sessions, rows that are being modified are logically deleted from load-isolated tables and new rows with the modified values are inserted when rows are updated. If a row is not yet committed, then the data that is read is the data that was true and final in the table prior to the subsequent data change [2].TJ maintenance is not an exception even for load-isolated tables in Teradata. TJ, though unavoidable, incurs extra costs to the system in the form of IO and CPU. Additionally the log needs to be purged periodically to cleanup records no longer needed causing additional overhead. This paper discusses an approach to avoid logging, with no compromise in data consistency, using Teradata Load Isolation, resulting in (a) significant improvement in the performance and transaction throughput, and (b) constant time (O(1)) transaction rollback, unlike the traditional approach(O(n)), where n is the number of rows.