Jonathan Lewis “Indexing Strategies” – May 23, 2013


One of the most important, yet overlooked aspects of efficiency is the need for a good indexing strategy.

Indexes improve the speed of queries, but increase the cost of DML. Given a thorough understanding of indexes you have the option for deciding the optimal set of indexes to suit the needs of your application. Everything you might want to know about indexes will be in this tutorial.

1 - The Structure of Indexes

What is a B-tree index, what is a Bitmap index and why are the two so different? How does Oracle build indexes? Are there differences in reverse indexes, function-based indexes, cluster indexes, global partitioned indexes, indexed organized tables, and secondary indexes? With the aid of a few block dumps and carefully monitored test results we examine the way that different types of indexes are built and behave.

2 - The Uses of Indexes

How many different ways can Oracle take advantage of an index?  We have primary keys and unique keys - do we need unique indexes to enforce them. We may have problems with foreign keys if we don’t have related indexes. We need optimal access paths – but will indexing help and what will it cost? When will Oracle use an index to accelerate a query?  Can we assist performance by understanding the mechanisms?

3 - Popular misconceptions

There are several surprising ideas floating around the Internet about how Oracle indexes work. Sometimes these ideas are irrelevant and have no use or impact. Sometimes they have a serious impact on your effectiveness. They may cause you to do work that need not, or should not, be done. They may introduce down-time that you do not need. They may make you avoid a feature that is really the perfect feature for your application. This session examines some of the more popular and potentially damaging misconceptions about indexes.

4 - Maximum benefit, minimum cost

We all know that indexes are supposed to make things go faster – but sometimes we forget that they have maintenance costs – of two types. How do you identify the optimal set of indexes for your application? How do you identify redundant indexes? How do you check whether or not an index has become inefficient and needs to be rebuilt – and how do you rebuild it most cost-effectively? This session discusses strategies for ensuring that you don’t waste effort supporting indexes that shouldn’t exist, and don’t waste valuable batch time rebuilding indexes that don’t need it.