# Determining Index Usage - Teradata Database

Product
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

### Determining Index Usage

To determine if the Optimizer will use an index to process a request, specify a WHERE clause for the query of interest based on an index value, any covering column in the index, or both, and then specify the EXPLAIN request modifier to determine whether the specified index value affects path selection.

When you specify an indexed column in a request, semantically equivalent queries specified using different syntax can cause the Optimizer to generate different access plans. For example, the Optimizer might generate different access paths based on the following forms, and depending on the expression, one form might be better than the other.

`     Form 1.    (A OR B) `
`               AND `
`                (C OR D)`
`     Form 2     (A AND C)`
`               OR `
`                (A AND D) `
`               OR `
`                (B AND C) `
`               OR`
`                (B AND D)`

In expressions involving both AND and OR operators, the Optimizer generates the access path based on the form specified in the query. The Optimizer does not attempt to rewrite the code from one form to another to find the best path. Consider the following condition.

`        ( NUSI = 7 `
`       OR NUSI = 342) `
`      AND (X = 3 `
`        OR X = 4)`

In this case, Form 1 is optimal, because the access path consists of two NUSI SELECT operations with values of 7 and 342. The Optimizer applies `(X=3 OR X=4)` as a residual condition. If the Optimizer uses Form 2, the access path consists of 4 NUSI SELECT operations.

In the following condition, the collection of (NUSI_A, NUSI_B) comprises a NUSI. Form 2 is optimal because the access path consists of 4 NUSI SELECT operations, whereas the Form 1 access path requires a full‑table scan.

`         (NUSI_A = 1 `
`          OR NUSI_A = 2) `
`     AND (NUSI_B = 3 `
`          OR NUSI_B = 4)`

Assume an expression involves a single-column comparison using an IN clause such as the following

`     column IN (value_1, value_2, ...)`

The Optimizer rewrites that expression to the following form.

`        column = value_1 `
`     OR column = value_2 `
`     OR column = value_m`

Therefore, the Optimizer generates the same access path for either form. However, if an expression involves a multicolumn comparison using an IN clause, such as in the following query, then the Optimizer rewrites the expression to form b.

`a.          (column_1 IN ( value_1 `
`                        OR value_2 `
`                        OR value_n)         AND column_2 IN ( value_3 `
`                        OR value_4 `
`                        OR value_n) `
` `
`b.        (  column_1 = value_1 `
`          OR column_1 = value_2 `
`          OR column_n = value_m)         AND `
`          (  column_2 = value_3 `
`          OR value_n)`

Notice that the rewritten form differs from the following condition, which is in Form 2).

`c.          (    column_1 = value_1 `
`             AND column_2 = value_3)         OR (    column_1 = value_2 `
`             AND column_2 = value_4)         OR      column_n = value_m`

For more information about the EXPLAIN request modifier, see SQL Request and Transaction Processing and SQL Data Manipulation Language.