Re: How many people here rebuid index regularly?

On Oct 6, 5:13 pm, csn..._at_gmail.com wrote:
> > I realise this would take time, but have you considered a treedump of
> > the indexes before and after the rebuild:
>
> > alter session set evnts = 'immediate trace name treedump level <index
> > object id>';
>
> > You should see considerable differences in the dump files for a given
> > index if the rebuild of it improves performance. And those file pairs
> > with differences would indicate the indexes upon which you should
> > concentrate your rebuild efforts.
>
> Comparing index sizes before and after rebuild would probably reveal
> all there is to know.

No, it wouldn't be all there is to know.

If an index settles down at about 75% space utilisation in a steadystate  scenario, it stands to reason that a rebuild will show it small after the rebuild that before. But if you were to conclude from that that the rebuild was obviously of benefit, and that the reduction in size was "all you needed to know", you'd be missing a trick or three.

Does the now-compacted index now suffer from higher contention rates than it used to? It could well do so. Do the next X-number of inserts to the table now take place slower than they did before because they now have to cause the index to re-acquire the empty space it previously already had available to it? It could well be the case.

Size is NOT everything, in short. Received on Sat Oct 06 2007 - 05:05:01 CDT