Oracle Scratchpad

January 26, 2023

Indexing foundations

Filed under: Indexing,Oracle — Jonathan Lewis @ 6:08 pm GMT Jan 26,2023

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.

1 Comment »

  1. […] Foundation thoughts (Jan 2023): before you commit to creating specific indexes on a table take a moment to go back to general principles and review your strategy. […]

    Pingback by Indexing Catalogue | Oracle Scratchpad — January 26, 2023 @ 6:14 pm GMT Jan 26,2023 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: