DWHPro Insights

Teradata Index Selection: Factors to Consider

By Roland Wenzlofsky ·

Teradata Index Selection: Factors to Consider

The Teradata Tutorial to support the selection of indexes

How much space does the Teradata index occupy?

Measure index storage and maintenance on the actual platform and compression configuration. Fallback adds protection and work, but fixed space/I/O multipliers are simplified models, not universal measurements.

On what kind of table is the index needed?

A USI provides enforced uniqueness and direct access on MULTISET as well as SET tables. Only SET duplicate-row-check avoidance is irrelevant for MULTISET. Also verify compatibility with the selected load operator.

How many rows are in the table?

The size of the table influences the space needed for the index.

Is Fallback Protection activated?

Measure index storage and maintenance on the actual platform and compression configuration. Fallback adds protection and work, but fixed space/I/O multipliers are simplified models, not universal measurements.

How often are INSERT, UPDATE, or DELETE statements executed?

Indexes add maintenance work, but do not universally double I/O. The work depends on index design, operation and whether indexed values change; unchanged values do not necessarily require rewriting every index row.

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.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam