Comparison of Index Types - Advanced SQL Engine - Teradata Database

Database Introduction

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qia1556235689628.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1091
lifecycle
previous
Product Category
Teradata Vantage™

Teradata Database does not require or allow users to explicitly dictate how indexes should be used for a particular query. The Optimizer costs all of the reasonable alternatives and selects the one that is estimated to be the least expensive.

The object of any query plan is to return accurate results as quickly as possible. Therefore, the Optimizer uses an index or indexes only if the index speeds up query processing. In some cases, the Optimizer processes the query without using any index.

Optimizer index selection for a query plan:
  • Can have a direct impact on overall Teradata Database performance.
  • Is not always a straightforward process.
  • Is based partly on usage expectations.

The following table assumes execution of a simple SELECT statement and explains the strengths and weaknesses of some of the various indexing methods.

This access method… Has the following strengths… And the following possible drawbacks…
Unique Primary Index (UPI)
  • is the most efficient access method when the SQL statement contains the PI value
  • involves one AMP and one row
  • requires no spool file (for a simple SELECT)
  • can obtain the most granular locks
none, in the context of a SELECT statement specifying a PI value. However, a poorly chosen PI can cause poor overall performance in a large workload.
Nonunique Primary Index (NUPI)
  • provides efficient access when the SQL statement contains the PI value
  • involves one AMP
  • can obtain granular locks
  • may not require a spool file as long as the number of rows returned is small
  • may slow down INSERTs for a SET table with no USIs.
  • may decrease the efficiency of SELECTs containing the PI value when some values are repeated in many rows.
Unique Secondary Index (USI)
  • provides efficient access when the SQL statement contains the USI values, and you do not specify PI values
  • involves two AMPs and one row
  • requires no spool file (for a simple SELECT)
requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements.
Nonunique Secondary Index (NUSI)
  • provides efficient access when the number of rows per value in the table is relatively small
  • involves all AMPS and probably multiple rows
  • provides access using information that may be more readily available than a UPI value, such as employee last name, compared to an employee number
  • may require a spool file
  • requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements.
  • will not be used by the Optimizer if the number of data blocks accessed is a significant percentage of the data blocks in the table because the Optimizer will determine that a full table scan is cheaper.
Full table scan
  • accesses each row only once
  • provides access using any arbitrary set of column conditions
  • examines every row.
  • usually requires a spool file possibly as large as the base table.
Multitable join index (JI)
  • can eliminate the need to perform certain joins and aggregates repetitively
  • may be able to satisfy a query without referencing the base tables
  • can have a different PI from that of the base table
  • can replace an NUSI or a USI
  • requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements for any of the base tables that contribute to the multitable JI.
  • usually is not suitable for data in tables subjected to a large number of daily INSERT, UPDATE, MERGE, and DELETE statements.
  • imposes some restrictions on operations performed on the base table.
Single-table join index (JI)

or

hash index

  • can isolate frequently used columns (or their aggregates for JIs only) from those that are seldom used
  • can reduce number of physical I/Os when only commonly used columns are referenced
  • can have a different PI from that of the base table
  • requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements.
  • imposes some restrictions on operations performed on the base table.
Sparse join index (JI)
  • can be stored in less space than an ordinary JI
  • reduces the additional overhead associated with INSERT, UPDATE, MERGE, and DELETE statements to the base table when compared with an ordinary JI
  • can exclude common values that occur in many rows to help ensure that the Optimizer chooses to use the JI to access less common values
  • requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements to the base table.
  • imposes some restrictions on operations performed on the base table.