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;