Hash indexes remain supported: creation executed in the isolated fixture. Single-table join indexes offer overlapping and broader capabilities, but have not made hash indexes nonexistent.
1. FULL TABLE SCAN
This technique enables scanning each row only once and grants access to any combination of columns as specified in the WHERE clause. It is the most suitable approach when all rows need to be read without exception.
Scan spool size depends on filtering, projection, aggregation and downstream steps. It need not equal or exceed base-table size.
2. UNIQUE PRIMARY INDEX (UPI)
Simple unique-key lookup can avoid a spool, but that depends on the complete query shape. USI access can involve an index-owning AMP and a base-row AMP, which may coincide; exact AMP counts are not universal for the whole request.
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 bookNone.
3. NON-UNIQUE PRIMARY INDEX (NUPI)
It offers efficient access using only one AMP and enables row-hash locking based on the number of rows selected, which may necessitate a spool file.
NUPI’s SET tables may suffer from inadequate INSERT performance due to duplicate row checks.
4. UNIQUE SECONDARY INDEX (USI)
Index maintenance causes extra overhead for INSERT, UPDATE, MERGE, and DELETE actions.
5. NON-UNIQUE SECONDARY INDEX (NUSI)
NUSI usefulness depends on selectivity, coverage, row/block distribution and other predicates, not simply low distinctness. Compare its cost with the base-table access plan.
It incurs overhead for INSERT, UPDATE, MERGE, and DELETE statements. However, the optimizer avoids using it when accessing many data blocks, since it requires accessing all AMPs. In such cases, a full table scan is often more cost-effective.
6. SINGLE-TABLE JOIN INDEX
Limiting index columns to frequently used ones reduces IOs. The primary index in the index table may differ from the base table.
It incurs overhead for INSERT, UPDATE, MERGE, and DELETE queries.
7. MULTI-TABLE JOIN INDEX
This obviates the need for performing joins or aggregates and can potentially fulfill intricate queries without referencing the source table. Additionally, it can have a unique PI compared to the source table and act as a replacement for NUSI and USI.
Multi-Table Join Indexes result in additional workload for INSERT, UPDATE, MERGE, and DELETE statements that involve any associated base tables. It is recommended to avoid utilizing them for tables that undergo substantial modifications.
8. SPARSE JOIN INDEX
The overhead for statements such as INSERT, UPDATE, MERGE, and DELETE is minimized by limiting the storage of index rows compared to a standard join index. Furthermore, the optimizer can utilize it to access infrequent values, given that it is restricted to selective column values.
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements.
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.
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