15.00 - Column Candidacy for Covered Access - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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 fully 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;