GeezerVison

Covering indexes are a crucial performance technique for RDBMS optimization, and one of the most effective tools in the tuning toolbox. When large joined queries suffer from poor performance, here are some tips to tackle the situation.

Covering Index basics

Covering indexes are indexes which “cover” all columns needed from a specific table, removing the need to access the physical table at all for a given query/ operation.

Since the index contains the desired columns (or a superset of them), table access can be replaced with an index lookup or scan — which is generally much faster.

Columns to cover:

• parameterized or static conditions;   restricting by a parameterized or constant condition.
• join columns;   columns dynamically used for joining
• selected columns;   to answer selected values.

While covering indexes can often provide good benefit for retrieval, they do add somewhat to insert/ update overhead; due to the need to write extra or larger index rows on every update.

Covering indexes for Joined Queries

Covering indexes are probably most valuable as a performance technique for joined queries. This is because joined queries are more costly & more likely then single-table retrievals to suffer high cost performance problems.

• in a joined query, covering indexes should be considered per table.
• each ‘covering index’ removes a physical table access from the plan & replaces it with index-only access.
• investigate the plan costs & experiment with which tables are most worthwhile to replace by a covering index.
• by this means, the multiplicative cost of large join plans can be significantly reduced.

For example:

select oi.description, c.name, c.address
from porderitem poi
join porder po on po.id = poi.fk_order
join customer c on c.id = po.fk_customer
where po.orderdate > ? and po.status = 'SHIPPING';

create index porder_custitem on porder (orderdate, id, status, fk_customer);

Have you used or needed covering indexes? Add your comments below.