The Teradata Tutorial to support the selection of indexes
An index can take up surprisingly much space, especially if the Teradata system does not automatically apply Block Level Compression to the index. A NUSI can then take up many times the space of the base table because multivalue compression is not applied to a NUSI!
Depending on the type of table, different considerations are necessary as to how the index should be used.
For example, a USI can be used on a SET table to prevent the duplicate row check, while this makes no sense on a MULTISET table.
If it is a staging table, it is necessary to consider to what extent the index is compatible with the loading tool. For example, when using the Multiload, there may not be a USI on the target table, but there may be a NUSI.
The size of the table has an influence on the space needed for the index.
Depending on the type of index, it is also Fallback Protected. This should be taken into account when calculating the required space. It also means that each index row for DML statements must be processed twice.
For each DML statement, the index rows must be maintained. This doubles the number of IOs.
Which type of index is used and how depends on the workload. For example, a USI is best suited for tactical workloads. Row-level partitioning is again ideal for strategic queries in which ranges of data (e.g. the days of a year) are processed.
Since the advantage of indexes is mostly the direct access via hashing, it is important to consider which columns are particularly suitable for this purpose, since they are often used in WHERE conditions.
The more columns a secondary index contains, the more specific it is and the fewer queries can use it. On the other hand, a multi-column index can better identify correlations between columns.
These ratios help to determine the selectivity of the index.
This key figure helps to estimate, for example, in NUSI whether the optimizer will use the index.