The optimizer has various access paths to choose from when creating the execution plan. Each of these access paths has certain advantages and disadvantages.
In this blog post I will introduce you to the 8 most important access paths. Do not be surprised that the hash index is not included.
Teradata has completely replaced the hash index by the single-table join index and recommends in the latest documentation not to use it anymore. The single table join index can do everything the hash index can do and much more.
1. FULL TABLE SCAN
Reads each row only once and provides access for any combination of columns in the WHERE condition. Is is the best choice if all rows need to be read anyway.
It reads each data block of a table and usually requires a spool table as large as the base table
2. UNIQUE PRIMARY INDEX (UPI)
It is the cheapest access path and used when the query contains all the primary index columns in the WHERE condition. It only reads from one AMP. No spool file is created. It uses row hash locking, which is the most granular locking method available.
None.
4. UNIQUE SECONDARY INDEX (USI)
Provides an efficient access path if the queries WHERE condition contains all the USI columns. Typically involves only two AMPs. No spool table is required.
Causes additional overhead for INSERT, UPDATE, MERGE, and DELETE statements as the index has to be maintained.
5. NON-UNIQUE SECONDARY INDEX (NUSI)
Provides efficient access when the number of distinct values is low, although it requires all AMPs and may create a spool table.
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements. It will not be used by the optimizer if the number of data blocks accessed is high, as it's an All-AMP access and often a full table scan often is cheaper.
6. SINGLE-TABLE JOIN INDEX
Ideal when restricting the index columns only to the often used columns as this reduces the number of IOs. It can have a different primary index than the base table.
It causes overhead for INSERT, UPDATE, MERGE, and DELETE statements.
7. MULTI-TABLE JOIN INDEX
Eliminates the need to perform joins and aggregates, and may be able to satisfy a complex query without referencing the base table. It can have a different PI than the base table and can replace an NUSI or a USI.
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements for any of the base tables being part of the Multi-Table Join Index. It is a bad choice for tables with a huge number of changes.
8. SPARSE JOIN INDEX
Limits the number of index rows being stored compared with a normal join index. This limits the overhead for INSERT, UPDATE, MERGE, and DELETE statements. If restricted to highly selective column values, it can be used by the optimizer to access less frequent values.
Causes overhead for INSERT, UPDATE, MERGE, and DELETE statements.