5.3 Covering Indexes

5.3 Covering Indexes

An index can answer an EXISTS() or NOT EXISTS() predicate for its base table immediately if the column(s) upon which it is built are used in the query. The index will contain at least one occurrence of a value of that attribute if and only if it is in the base table. A dense index is also able to perform aggregate functions on its base table column(s) quickly because all of the value(s) are physically contiguous in the index file. The base table itself is never read.

A sparse index can sometimes answer an EXISTS() predicate for its base table value(s) immediately, if you got lucky and asked for one of the indexed values. But most of the time, you have to read the base table itself.

A covering index is an index built on multiple columns that are all used in a query. A common example is (last name, first name) in tables that deal with people. Queries might be done on just the last name, but the display is almost always of both first and last name together. In this case, you are depending on the indexing method to allow the first part of the index, the last name, to be searched so that the first name will be brought along with it.

< Previous Excerpt Next Excerpt >

Purchase This Book

Joe Celko's Data and Databases: Concepts in Practice

TABLE OF CONTENTS

5.3 Covering Indexes

Contact Preferences

This is embarrasing...

Customize Your GlobalSpec Experience

Select Your Free Newsletters

Industry Newsletters

Select Your Free Product Alerts

This is embarrasing...