Optimized Performance Using a NUSI

Teradata Vantage™ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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.