Secondary Index in Teradata

A minimum set of secondary indexes should be determined for each table. These would include those columns described above, which are subdivisions of primary indexes used in table joins. Also, an additional set of secondary indexes could include those columns used for set selection in the where clause, and those non-index columns used for table joins.

TOS(Teradata Operating System) will not subdivide a primary index into its components and do a partial index search or retrieval.

If a unique primary index is composed of several columns, then subsets of those columns are likely candidates for non-unique secondary indexes. When a query uses a join to a subset of the columns of the primary index, then the Teradata Operating System will not subdivide the primary index into a subdivision of columns, and use this subdivision as an index. Instead, a secondary index must be specifically created with the columns contained in the subdivision. Then the optimizer may select the secondary index for processing the query.

The selection of the additional set of indexes depends on the column usage within a query, and whether the optimizer will select the index, even if it is available. the explain command provides an insight to this selection.

Tweet