I’m encroaching on Richard Foote’s territory here – with plans to write a few details about some of the implementation details of Oracle’s B-tree indexes. My strategy, though, is to entertain by asking a few questions that might prompt a little speculation before giving some answers. So …
After running validate index against a particular index on my system, I select the following columns from view index_stats: (using Tom Kyte’s invaluable “one column per line” routine).
HEIGHT : 3 BLOCKS : 640 LF_ROWS : 100008 LF_BLKS : 601 LF_ROWS_LEN : 1900152 LF_BLK_LEN : 3992 DEL_LF_ROWS : 0 DEL_LF_ROWS_LEN : 0 DISTINCT_KEYS : 100008 MOST_REPEATED_KEY : 1 BTREE_SPACE : 2423288 USED_SPACE : 1908481 PCT_USED : 79
Looking at these results, what would your opinion be about rebuilding this index ?
- Definitely not
- Probably not
- Small bias against
- Insufficient information
- Small bias for
- Probably would
- Definitely would
Don't forget to justify your opinion.
As a bonus question - what do you think my blocksize was ?
Footnote (or possibly Foote note): Richard Foote is not allowed to answer the question - as he probably knows where I am going with this set of posts.
Update: My final answer is in comment 9.