Index Efficiency
I was looking for an old posting on the Oracle newsgroup recently, when I found a set of posts that talked about measuring the effectiveness of rebuilding some indexes.
I had contributed by pointing to my website where I have a script that demonstrates how to get some detailed information about how well the space in an index is used, and Joel Garry had responded with some results from a production instance.
The full thread starts at this URL
But for a quick summary of the conversation I had with Joel, you could go to each of these posts in turn:
- My comments about doing a tree dump of an index
- Introducing my “leaf block scan” code:
- Joel’s repsonse after using the “leaf block scan” code
- My analysis of Joel’s output
- Joel’s response to my analysis
For other notes I’ve written about index rebuilds:
I was looking for this!
Thant you very much.
Comment by lascoltodelvenerdi — May 15, 2008 @ 12:32 pm UTC May 15,2008
Still much appreciated. I’ll be importing this data into new hardware/app version/O10g in a month and a half, but I don’t expect the app will do anything different there regarding these indices. In case anyone wondered, I haven’t rebuilt them, they’ll be imp’d soon enough, and I’ll have a lot more space anyways (uh oh, sounds like famous last words).
Comment by joel garry — May 15, 2008 @ 9:09 pm UTC May 15,2008
Hello Jonathan,
What about the following calculation.
Sum the average column length of all columns that make up the index.
Add 10 for the rowid to this summed column length.
Multiply this with a factor to account for overhead (I have taken 1.3).
Multiply this by the number of rows from dba_indexes this would give us the number of bytes needed for the leaf blocks.
Compare this to the real number of leaf blocks.
This does not account for the fact that columns can have null values in concatenated indexes.
Regards Hans-Peter
Comment by Hans-Peter Sloot — May 22, 2008 @ 8:31 am UTC May 22,2008
Hans-Peter,
It looks like we’ve had similar thoughts in the past. There is a second post on my website that describes that strategy and supplies some SQL for simple B-tree indexes.
Comment by Jonathan Lewis — May 22, 2008 @ 10:08 am UTC May 22,2008