The Teradata Tutorial to support the selection of indexes
How much space does the Teradata index occupy?
An index can take up surprisingly much space, especially if the Teradata system does not automatically apply Block Level Compression to the index. MANY TIMES, a NUSI can take up the base table’s space because multivalue compression is not applied to a NUSI!
On what kind of table is the index needed?
Depending on the table type, different considerations are necessary regarding how the index should be used.
For example, a USI can be used on a SET table to prevent the duplicate row check, making no sense on a MULTISET table.
If it is a staging table, it is necessary to consider how 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.
How many rows are in the table?
The size of the table influences the space needed for the index.
Is Fallback Protection activated?
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.
How often are INSERT, UPDATE, or DELETE statements executed?
For each DML statement, the index rows must be maintained. This doubles the number of IOs.
Are strategic or tactical queries performed?
Which type of index is used and how it 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 data ranges (e.g., the days of a year) are processed.
Which columns are used in WHERE conditions?
Since the advantage of indexes is mainly the direct access via hashing, it is crucial to consider which columns are particularly suitable for this purpose since they are often used in WHERE conditions.
Should I use a Teradata Multi-column or single-column index?
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.
What are the maximum rows and typical rows per value?
These ratios help to determine the selectivity of the index.
How many distinct values do the index columns contain?
For example, this key figure helps estimate, for example, in NUSI, whether the optimizer will use the index.