15.00 - Join Index Costs Summary - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Join Index Costs Summary

Creation and maintenance costs for join indexes can be a resource burden because of their processing overhead. In the case of join index maintenance, the burden is an ongoing process that lasts for the life of the index.

Costs vary considerably among the various types of joins used as well as between simple and aggregate types. The following bulleted list summarizes the conclusions to be drawn from the performance analyses performed.

  • Maintenance overhead for in-place aggregate join indexes ranges from 1.0 to 2.9.
  • Maintenance overhead for aggregate join indexes that redistribute rows ranges from 2.6 to 9.5.
  • Maintenance overhead for simple join indexes ranges from 1.0 to 23.1, increasing as the number of hits per data block increases.
  • Maintenance overhead for aggregate join indexes decreases as the number of hits per data block increases because of the efficiencies of block-at-a-time operations in the file system.
  • Maintenance overhead for insert operations is significantly less than the maintenance overhead required for update and delete operations.