February 17

0 comments

Teradata I/O Optimization

By DWH Pro Admin

February 17, 2020


Why I/Os are so important in Teradata

The minimization of I/Os in Teradata is one of the most important goals in performance tuning. I/Os are necessary to copy data from storage to main memory. Data can only be processed if it is in the main memory.

The process of copying data into the main memory has always been countless times slower than accessing data in the main memory or in the CPU cache.

Performance can, therefore, be optimized enormously by minimizing the I/Os. That is why almost all modern OLAP databases (Snowflake, Redshift, etc.) are column stores that copy only the required columns into the main memory.

Logical vs. Physical I/Os in Teradata

In this blog post, I will show which operations cause how many I/Os and why.

However, it is important to understand that not all I/Os have to be done because the data might already be in memory. Since Teradata does different kinds of caching we will only refer to the logical I/Os in this blog post. The physical I/Os may be lower because cache hits may prevent reading from the storage.

The most important questions about Teradata I/Os

What types of I/O are caused by indexes?

UPDATE, INSERT and DELETE statements on base tables cause additional I/O if indexes exist on the table.

These include a secondary index, join indexes, hash index.

It should also be remembered that indexes can be nested. So a secondary index can be created on a join index, which further increases the number of I/Os required.

How does a permanent journal in Teradata affect the I/Os?

Permanent journaling causes additional I/Os. 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 I/Os does the Teradata Transient Journal cause?

The Transient Journal must keep a copy (Before Image) of all rows that are 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 the case 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 I/Os 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 I/Os causes INSERT and DELETE into a base table?

There are 5 I/Os 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 changed data block is written
– The Cylinder Index is written

How many I/Os causes INSERT and DELETE into a USI subtable?


There are 5 I/Os 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 I/Os causes INSERT and DELETE into a NUSI subtable?


There are 3 I/Os 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 I/Os 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 I/Os (5 I/Os + 5 I/Os):

I/Os 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

I/Os 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

I/Os 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 I/Os causes the UPDATE in a base table if the primary index is unchanged?


I/Os 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

I/Os 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

I/Os 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 I/Os?

The number of I/Os 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)

DWH Pro Admin


Teradata and Data Warehouse Specialist with more than 20 years of experience

You might also like

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

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!

>