17.10 - NUSIs and Query Covering - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

The Optimizer aggressively pursues NUSIs when they can cover a query. 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.

Covering of a query can also be partial. In the case, 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.

Example: Index Used to Cover a 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;