Determining Index Usage - Teradata Database

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.