Abstract

The purpose of indexing is to give us precision access to the data we find interesting; better precision gives us greater efficiency, leading to better performance for queries. To get this precision, though, we have to maintain - in real time - the structures that give us that precision; and this slows down data maintenance. This conflict of interest makes it difficult to pick the best set of indexes when we design a system - how many indexes can we add to assist queries before the maintenance cost is too high; can we re-arrange the column order of an existing index or add extra column, rather than adding yet another index to the system.

The problems of indexing become much harder after a system has been in production for some time - is it safe to drop an index that doesn't seem to be useful, how can we even identify an index that might be safe to drop, will there be side effects that we hadn't predicted if we do drop the index, and if we add a new index for one reason will it result in other queries behaving badly.

In this presentation we review the way we think about indexes, and examine possible methods of getting more value for less work by making better use of the tools that Oracle has given us.