February 6


Teradata Tutorial – Indexing

By DWH Pro Admin

February 6, 2020

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. A NUSI can then take up many times the space of the base table because multivalue compression is not applied to a NUSI!

On what kind of table is the index needed?

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.

How many rows are in the table?

The size of the table has an influence on 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 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.

Which columns are used in WHERE conditions?

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.

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?

This key figure helps to estimate, for example, in NUSI whether the optimizer will use the index.

DWH Pro Admin

Teradata and Data Warehouse Specialist with more than 20 years of experience

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!