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) |
|
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) |
|
|
Unique Secondary Index (USI) |
|
requires additional overhead for INSERT, UPDATE, MERGE, and DELETE statements. |
Nonunique Secondary Index (NUSI) |
|
|
Full table scan |
|
|
Multitable join index (JI) |
|
|
Single-table join index (JI) or hash index |
|
|
Sparse join index (JI) |
|
|