This section covers index tuning, which is an important facet of query tuning. Indexes are sorting and searching structures. They reduce the need for I/O when looking for data and for sorting when certain elements in the plan need or can benefit from sorted data. While some aspects of tuning can improve performance by a modest percentage, index tuning can often improve query performance by orders of magnitude. Hence, if you’re in charge of tuning, learning about indexes in depth is time well spent.
I’ll start by describing table and index structures that are relevant for our discussions. Then I’ll describe index access methods used by the optimizer and conclude the section with an analysis of indexing strategies.