The optimizer has various access paths to consider when creating the execution plan, each with unique advantages and disadvantages. This article will introduce the 8 most essential access paths. The lack of a hash index may surprise you; however, Teradata has entirely substituted it with the single-table join index and discourages its utilization in the newest documentation. The single-table join index can execute all the hash index functions and more.
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.
The program reads each data block from a table and usually requires a spool table that is equal to or greater in size than the original table.
2. UNIQUE PRIMARY INDEX (UPI)
The most economical way to access data is by utilizing the primary index columns in the WHERE condition. This method exclusively accesses a single AMP and does not produce a spool file. It uses row hash locking, which provides a highly precise approach.
None.
3. NON-UNIQUE PRIMARY INDEX (NUPI)
It offers streamlined access with only one AMP and enables row-hash locking based on the selected number of rows, 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)
This method optimally retrieves queries with a WHERE condition, including all USI columns, using only two AMPs, eliminating the need for a spool table.
Index maintenance causes extra overhead for INSERT, UPDATE, MERGE, and DELETE actions.
5. NON-UNIQUE SECONDARY INDEX (NUSI)
Facilitates access efficiently with low distinct values, but requires utilization of all AMPs, which may lead to a spool table.
Implementing this feature for INSERT, UPDATE, MERGE, and DELETE statements results in overhead. However, the optimizer avoids utilizing it when accessing many data blocks as 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.
