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.

How 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 must keep a copy (Before Image) of all rows changed by a DML statement. This allows a ROLLBACK in case of an error. The copies are kept in the WAL (Write Ahead Log) until a transaction is committed.

In WAL, a copy of the changed data block is also kept (after image) if the changes can be written back to the original data block. If the changes require a restructuring of the data blocks, the after image is not necessary.

This can be if a row does not fit into the original data block after an update.

The after image is used to have a copy available if errors occur when overwriting the original data block.

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

Correctly it must be said that exactly one row is written to the transient journal when a table is completely deleted. But not for every row deleted, one entry is kept in the WAL.

How many IOs causes INSERT and DELETE into a base table?

There are 5 IOs needed for changing a base table row:
– The data block is read
– The “before image” is written into WAL
– The data block in main memory is changed (insert or delete), and an “after image” is written to the WAL log
– The modified data block is written
– The Cylinder Index is written

How many IOs causes INSERT and DELETE into a USI subtable?


There are 5 IOs needed for changing a new USI subtable row:

– The USI subtable data block is read
– A “before image” is written to the WAL
– The subtable data block is changed (insert or delete), and an “after image” is written into the WAL
– The USI subtable data block is written
– The Cylinder Index is written

How many IOs causes INSERT and DELETE into a NUSI subtable?


There are 3 IOs needed for adding a new USI subtable table row (or rowid to an existing index row):

– The NUSI subtable data block is read. The ROWID is deleted or added to the list of ROWIDs, or a NUSI subtable row is added or removed.
– The NUSI subtable data block is written
– The Cylinder Index is written

How many IOs causes the UPDATE in a base table if the primary index is changed?


The update of the Primary Index actually corresponds to a DELETE and INSERT. The row must be deleted on the existing AMP and created on the new AMP. Therefore this is equivalent to an INSERT and DELETE operation and requires 10 IOs (5 IOs + 5 IOs):

IOs needed for the base table:

DELETE step(first AMP):

– The current data block is read
– The “before image” is written in WAL
– DELETE row and write the after image WAL
– Write the new data block
– Write Cylinder Index

INSERT step(second AMP):
– The receiving (new) data block is read
– The “before image” is written in WAL
– INSERT row and write to after image WAL
– Write new data block
– Write Cylinder Index

IOOs needed for each USI:

– Read the USI subtable data block
– Write before image to WAL
– UPDATE the subtable row with the new ROWID (pointing to the moved base table row) and write the after image to the WAL
– Write new subtable data block
– Write Cylinder Index

IOOs needed for each NUSI:

– Read NUSI subtable data block from AMP of current PI
– Read NUSI subtable data block from AMP for new value and update the list of rowids of both subtable blocks
– Write new NUSI subtable block (old AMP)
– Write new NUSI subtable block (new AMP)
– Write cylinder index (old AMP)
– Write Cylinder Index (new AMP)

How many IOs causes the UPDATE in a base table 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 number of IOs doubles. Note that not only the base table has fallback protection but also all indexes.

The ultimate Teradata Physical Storage guide – Part 2(Opens in a new browser tab)

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>