17.10 - Maintenance Cost as a Function of Row Size - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

The size of join index rows is another important factor to consider when evaluating the maintenance overhead of a join index. Given a constant disk space, as the size of the rows increases, the number of rows decreases, while the number of data blocks remains the same. The result is that the number of hits per data block decreases.

Maintenance Costs in Terms of CPU Path Per Transaction

CPU path per transaction times for updates increase steadily as a function of increased row size, while the number of transactions, or qualified rows, decreases drastically. This can be seen in the following graph, where CPU path per transaction is presented as a function of percentage of rows touched during an update operation.

Maintenance Costs in Terms of Other Measures

The following table indicates the trends in performance cost of join index maintenance as a function of increasing row size in terms of various performance measures:

Performance Measure Effect as a Function of Increasing Row Size
Elapsed time to insert, delete, or update join index rows. Decreases
I/Os per insert, delete, or update transaction Increases
Number of point-to-point BYNET insert, delete, or update transactions Increases


Summarizing these results, each join index maintenance transaction performs more work as a function of increased row size as measured by the following list of variables.

  • CPU path per transaction
  • I/Os per transaction
  • BYNET transactions per transaction