15.00 - Criteria - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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.