Jonathan Lewis: Indexing Strategies

What is a B-tree index, what is a Bitmap index and why is it 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.

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 fast 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?

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 misconceptionsabout indexes.

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 optimum 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.

• Indexes