15.00 - Selectivity of Indexes and Partitioning - Teradata Database

Teradata Database Design

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

Selectivity of Indexes and Partitioning

An index that retrieves many rows is said to have low selectivity. By definition, an index with low selectivity typically accesses one or more rows per data block from the table on which it is defined. If the average number of rows accessed per data block is appreciably higher than 1, you should consider dropping the index. However, this process is value-dependent, so you should evaluate the performance of the index in several different situations before dropping it.

An index that retrieves few rows is said to be highly selective. A highly selective index is one that does not access all of the data blocks for the table on which it is defined. An index that is intended to be highly selective should access significantly fewer than an average of 1 row per data block.

The more highly selective an index or partitioning is, the more useful it is for enhancing performance.

In some conditions, it is possible to link several nonunique secondary indexes with low selectivity together by bit mapping them. The result is effectively a highly selective index and a dramatic reduction in the number of table rows that must be accessed. See “NUSI Bit Mapping” on page 479 for further discussion of linking secondary indexes with low selectivity into a highly selective unit using bit mapping.