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

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

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;