16.10 - Maintenance Cost as a Function of Row Size - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

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

Summary

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