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.