Criteria
A NUSI covers a query if any of the following criteria are true:
The query does not reference any columns of the base table.
For example, SELECT COUNT(*) from table;
The NUSI includes all base table columns referenced in the query and any of the following
criteria are true:
The query does not reference any changeable character columns.
Changeable character columns are character columns that are not defined as CASESPECIFIC
or UPPERCASE. Teradata Database converts changeable character column data to uppercase
when it stores it in a NUSI defined without the ALL option. The data stored in the
index subtable might be different from the original lowercase data stored in the base
table.
This NUSI is defined with the ALL option.
This NUSI is not defined with the ALL option, and it contains a changeable character column set, but the changeable character columns are only specified in a COUNT function or UPPERCASE
operator in the query.
This NUSI is not defined with the ALL option and it contains a changeable character column set, and the changeable character column set is only specified in a COUNT function or UPPERCASE
operator in the select list, and there is no CASESPECIFIC condition on the changeable character column set in the
query conditions.
A partially‑covering NUSI is one that does not fully cover a query, but does satisfy both of the following criteria.
Some single‑table constraints of the query contain the NUSI column set.
If the NUSI contains a changeable character column set, the query does not specify
an inequality CASESPECIFIC condition on the changeable character column set, and no CASE expression is specified in a query condition that also specifies the changeable
character column set.