17.10 - Optimized Performance Using a NUSI - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1145-171K
Language
English (United States)

If it is cost-effective, the Optimizer may choose to evaluate a LIKE expression by scanning a NUSI with or without accessing the base table. The cost of using a NUSI depends on the selectivity of the LIKE expression, the size of the NUSI subtable, and if the NUSI is a covering index or a partially covering index. For a partially covering index, the cost of sorting the RowID spool is also included. For details on NUSIs and query covering, see Teradata Vantage™ - Database Design, B035-1094.

The Optimizer can perform a better cost comparison between using a NUSI and using an all-rows scan if the following are true:

  • There are statistics collected for both the base table primary index and for the NUSI columns against which the expression string is evaluated.
  • The expression string is either the mode or max value in at least one interval in the base table statistics histogram.

You cannot use a NUSI with a VARCHAR field for processing a LIKE expression when:

  • the NUSI contains a VARCHAR field, and the VARCHAR field is used in a NOT LIKE operation.
  • the NUSI contains a VARCHAR field, and the VARCHAR field is used in a string function.For example, the following is not allowed if d1 is a NUSI column of VARCHAR type.
    d1||‘ab’ LIKE ‘b ab’

In addition, a NUSI with a VARCHAR field cannot be used as a partially covering index for an unconstrained aggregate query.