Index Rebuild, the Need vs the Implications Support Note 989093.1 (Getting Better) March 5, 2014
Posted by Richard Foote in Doc 122008.1, Doc 989093.1, Index Rebuild, Oracle Indexes, Oracle Myths.trackback
Once upon a time, Oracle Support had a note called Script: Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1) which lets just say I didn’t view in a particularly positive light 🙂 Mainly because it gave dubious advice which included that indexes should be rebuilt if:
- Deleted entries represent 20% or more of current entries
- The index depth is more than 4 levels
It then detailed a script that ran a Validate Structure across all indexes in the database that didn’t belong in either the SYS or SYSTEM schema.
This script basically read through and sequentially locked all tables (maybe multiple times) in the database in order to list indexes that might not actually need a rebuild while potentially missing out on some that do. I could write a script that achieved the same result with far less overheads. For example, SELECT index_name FROM DBA_INDEXES where index_name like ‘A%’ and owner not in (‘SYS’, ‘SYSTEM’) would achieve a very similar result 🙂
Thankfully, note 122008.1 was eventually removed from My Oracle Support (MOS) some time ago, interestingly soon after I discussed the ramifications of this script in my Oracle Index seminars 🙂
I recently stumbled upon another related note on MOS regarding index rebuilds, Index Rebuild, the Need vs the Implications (Doc ID 989093.1). Although not perfect (for example while it mentions ANALYZE INDEX VALIDATE STRUCTURE can now be performed online, doing so means that INDEX_STATS is not populated making it a little pointless in this context), it is a significant improvement on the previous note and certainly well worth a read for Oracle newbies.
It also references a script to investigate a b-tree index structure (Doc ID 989186.1) that doesn’t rely on the Validate Structure of an index, making it a far less problematic to use, while also keeping a useful history of index characteristics. Also worth checking out.
Richard Foote's Oracle Blog 
Richard,
I’m glad you approve of note 989186.1 since the person who produced it copied the algorithms and code from my blog.
At one point (after I’d pointed this out a couple of times in the feedback form) the article acknowledged the IPR, but I see that that acknowledgement has disappeared.
See: http://jonathanlewis.wordpress.com/?s=989186
LikeLike
Hi Jonathan
I thought I’d seen that code somewhere before !!
Bit of a worry that the rightful achnowledgement has disappeared 😦
LikeLike
Thanks for the post. Plenty of chatter about how one does/does not need to be (not) worrying about index rebuilds.
The code in the MOS note doesn’t touch partitioned indexes, been (mostly wasting) time looking into the package and trying to figure out how a partitioned index, and/or subpartitioned indexes could be added to the ‘include …’ list.
Any place is walking distance, if [one] has the time (Wright, S. 198x. ???).
LikeLike
I’ve got 4 versions of the code – though I only published the simplest case. Rather than try to do everything in one package it’s better to keep it simple – so one version basically queries dba_ind_partitions, one queries dba_ind_subpartiitons, and the one for globally partitioned indexes has to be a little clever.
LikeLike
I agree with jonathan.I have attended his 2-day seminar on “Index Structures” in New York and I still remember that he’s the one who first explored the function “sys_op_lbid”. Anyways it’s not the first time that Oracle support copying someone else work without acknowledgement.I have one in my case too.
I am more interested in understanding on how to interpret the report,which is missing in the document. For example, what is the take away from index-layout? Assume there are not too many rows compacted in each leaf-blocks or the distribution is not even, will it be an indicator to rebuild the index?
LikeLike
[…] I replied “not yet” and basically committed to add something. This topic of the needs versus the implications of rebuilding an index has been recently discussed in Richard Foote’s Blog. In my opinion, if you want to know more […]
LikeLike