Indexes - Part 3 - Index Covering

What is Index Covering:

If the query references only those columns that are contained within a given index, then the query is said to be covering.

Such cases are more efficient because the optimizer has to access only the index subtable and does not need to access the base table rows at all.

Covering will be considered for any query that references only columns defined in a given NUSI. These columns can be specified anywhere in the query including the:

• SELECT list

• WHERE clause

• aggregate functions
• GROUP BY
• expressions

However having all the indexed columns does not guarantee that the optimizer will use index to cover the query. Whether or not the index is used we can check after looking at the explain plan

Example 1:

create index (departmentno) order by values on employee;

EXPLAIN select departmentno from employee where departmentno != 400;

Explain select departmentno from employee where departmentno != 400

  1) First, we lock a distinct MY_TEST_DB."pseudo table" for

     read on a RowHash to prevent global deadlock for

MY_TEST_DB.employee.

  2) Next, we lock MY_TEST_DB.employee for read.

  3) We do an all-AMPs RETRIEVE step from MY_TEST_DB.employee

     by way of a traversal of index # 4 without accessing the base 

     table with a residual condition of (

     "MY_TEST_DB.employee.DepartmentNo <> 400") into Spool 1

     (all_amps), which is built locally on the AMPs.  The size of Spool

     1 is estimated with no confidence to be 1,296 rows (32,400 bytes).

     The estimated time for this step is 0.01 seconds.

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.01 seconds.

Note the line where explain plan explicitly says that it would not access the base table.

This index is covering the query.