Indexing foundations

Here are the introductory comments I made at a recent “Ask me Anything” session about indexing arranged by the All India Oracle User Group:

There are 4 fundamental thoughts that you need to bear in mind whenever you’re thinking about what indexes your application needs:

1. The intent of indexing is to find the data you need with the minimum use of critical resources (which may be disk I/Os for relatively small systems, CPU for large systems).
2. High precision is important – the ideal use of indexes is to avoid visiting table blocks that don’t hold useful data. With ideal indexing, query performance is dictated by the amount of data you want to manipulate not by the total amount of data in the database.
3. Creating precise indexes for every query requirement leads to a lot of real-time maintenance when you modify data so you need to balance the resources needed for DML against the resources for queries.
4. Oracle offers many ways to minimise the work you need to do to use and to maintain indexes. (So you need to think about other mechanisms every time you think about adding indexes)

If you can keep these points in mind then everything else you need to think about for your specific system follows as a logical consequence.

I did consider adding one more comment about the most significant difference between B-tree and Bitmap indexes which is that individual B-tree indexes tend to be very precise while precision is only achieved with Bitmap indexes by combining them; but that’s just adding a specific example to a list of general principles and could even have the effect of deflecting people from the thoughts of combining (necesary but annoying) low-precision B-tree indexes.