16.10 - NUSIs and Query Covering - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

The Optimizer aggressively pursues NUSIs when they can cover a query. The expression covering means that all of the columns requested in a query or data necessary for satisfying the query are also available from an existing index subtable, making it unnecessary to access the base table rows themselves to complete the query. Some vendors refer to this as index-only access.

For column-partitioned tables, other access methods are available, and may be chosen by the Optimizer based on cost comparisons.

Covering of a query can also be partial, or an index can fail to cover any aspect of a query.

The Optimizer also selects a secondary index to process an aggregation on indexed values if the index covers the necessary values.

In the case of a partial covering index for single-table access, the system can get the row IDs for those base table rows that possibly qualify for a query by preliminary examination from the index, but then must also access the base table itself to retrieve the definitively qualified rows. Even a partial covering index can accelerate the processing of a query, especially when the base table being accessed is very large and the query constraints on the partially covering NUSI are highly selective. The optimizer determines if a partial covering index should be used based on the cost comparisons.

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.

Column Candidacy for Covered Access

The Optimizer attempts to use an index, if available, as a cover index,even when classical indexing techniques do not apply, because scanning an index is almost always faster than scanning the base table it references. The enhanced efficiency can result in significantly faster retrieval.

Example: Index Used to Cover Simple Query

The following example demonstrates a situation in which the Optimizer can use the index in place of the base table to satisfy the query.

     CREATE INDEX idxord (o_orderkey, o_date, o_totalprice)
     ON orders;
     SELECT o_date, AVG(o_totalprice)
     FROM orders
     WHERE o_orderkey >1000
     GROUP BY o_date;

Example: Index used to Cover Aggregate Query

Assume an index is defined on column deptno. The Optimizer can use that index and instruct the AMPs to aggregate on the index values, rather than using the more costly path of accessing the underlying base table.

     SELECT deptno, COUNT(*)
     FROM employee
     GROUP BY deptno;
   

Example: Index Values Can Be Manipulated Arithmetically

This example shows how index values can be manipulated arithmetically as well as being counted. In this case, an index is defined on salary_amount. The index alone can be used to satisfy this query.

     SELECT SUM(salary_amount)
     FROM employee
     GROUP BY salary_amount;