15.00 - Determining Index Usage - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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) 
                (C OR D)

     Form 2     (A AND C)
                (A AND D) 
                (B AND C) 
                (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 multi-column 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)
          (  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.