Why is Teradata I/O so crucial?
Minimizing Teradata I/O is a crucial aspect of performance tuning. IOs involve transferring data from storage to main memory, which is essential for Teradata to process data.
Transferring data to the main memory is significantly slower than accessing data in the main memory or CPU cache.
Minimizing IOs can greatly optimize performance. Most modern OLAP databases, such as Snowflake and Redshift, are column stores that solely copy necessary columns into the primary memory.
Difference between Logical vs. Physical Teradata I/O
The following post will illustrate the number of IOs caused by different operations and their reasons.
It is important to note that conducting every IO may be unnecessary as the data could already be stored in memory. Teradata utilizes various caching methods. Therefore, this post will solely focus on logical IOs. Physical IOs may be reduced due to cache hits that may eliminate the need to retrieve data from storage.
The most critical questions about Teradata I/O
What types of IO are caused by indexes?
UPDATE, INSERT and DELETE statements on base tables cause additional IO if indexes exist.
These include a secondary index, join indexes, hash index.
It should also be remembered that indexes can be nested. A secondary index can be created on a join index, which further increases the number of IOs required.
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 bookHow does a permanent journal in Teradata affect the IOs?
Permanent journaling causes additional IOs. How many depends on whether a before image, after image, or both are used and whether it is a single or dual image.
How many IOs does the Teradata Transient Journal cause?
The transient journal supports transaction rollback, while WAL supports recovery of storage updates. Their roles and records differ; do not equate them or assume a complete before-image per affected row in every operation.
When is no Transient Journal needed and no additional IOs are caused?
– INSERT/SELECT into an empty table
– DELETE all rows from a table
– Fastload and Multiload
To be precise, exactly one row is written to the transient journal when a table is completely deleted, not one entry per deleted row.
How many IOs does INSERT and DELETE into a base table cause?
The 3/5/10-I/O and doubled-fallback counts are simplified explanatory models, not invariant logical or physical measurements. Caching, batching, indexes, block changes and fast-path eligibility alter the cost. 
How many IOs does INSERT and DELETE into a USI subtable cause?
The 3/5/10-I/O and doubled-fallback counts are simplified explanatory models, not invariant logical or physical measurements. Caching, batching, indexes, block changes and fast-path eligibility alter the cost. 
How many IOs does INSERT and DELETE into a NUSI subtable cause?
The 3/5/10-I/O and doubled-fallback counts are simplified explanatory models, not invariant logical or physical measurements. Caching, batching, indexes, block changes and fast-path eligibility alter the cost. 
How many IOs does the UPDATE in a base table cause if the primary index is changed?
The 3/5/10-I/O and doubled-fallback counts are simplified explanatory models, not invariant logical or physical measurements. Caching, batching, indexes, block changes and fast-path eligibility alter the cost. 
How many IOs does the UPDATE in a base table cause if the primary index is unchanged?
IOs needed for the base table:
– Read data block
– Write before image into WAL
– UPDATE the base table row and write after image to WAL
– Write new data block
– Write Cylinder Index
IOs needed for each USI(if the column name is USI):
– Read current USI subtable data block
– Write before image into WAL
– Delete subtable row and write after image into WAL
– Write new USI subtable data block
– Write Cylinder Index
– Read current USI subtable data block
– Write before image into WAL
– Insert new subtable row and write after image into WAL
– Write new USI subtable data block
– Write Cylinder Index
IOs needed for each NUSI(if column name is NUSI)
– Read the current NUSI subtable data block, remove the base table ROWIDs or the whole subtable row if none is left
– Write NUSI subtable data block
– Write Cylinder Index
– Read the new NUSI subtable data block and add base table row ROWIDs or create a new subtable row
– Write NUSI subtable data block
– Write Cylinder Index
How does fallback protection affect the number of IOs?
The 3/5/10-I/O and doubled-fallback counts are simplified explanatory models, not invariant logical or physical measurements. Caching, batching, indexes, block changes and fast-path eligibility alter the cost.
The ultimate Teradata Physical Storage guide – Part 2(Opens in a new browser tab)
Empty-target INSERT SELECT, delete-all and utility optimizations have documented eligibility restrictions. No physical-I/O benchmark, journal-record inspection or recovery test was performed for these counts.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
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