Re: Index management

ademmerson wrote:

> I did a similar test some time ago and got the same surprise, so I did
> some research. The reason is that Oracle uses b*-tree indexes, not
> b-tree indexes. The organisation is subtly different and means that
> your "wicked, skewed bloated monstrosity of an index" is actually the
> exact opposite - as close to perfection as such an index can get. This
> is reflected in the fact that the result of an ordered insert is
> smaller than the result of a rebuild. If you don't believe me, try the
> next logical step and insert rows in DECREASING key order. Then you
> really do get a bad index and I certainly saw a reduction in size after
> a rebuild.

Crikey.... let's just leave descending indexes out of the equation for the moment! A few of us are trying to poke around inside them as we speak. However, because my basic test is just a simple script, and can therefore be re-run with variations with minimum fuss, I just did preciely as you suggested (ie, created an index on invoice number DESC), and got a reduction in size of 50%. So yes, they are horrible. Which is presumably why the 8.0 documentation (the last time I really looked at them) said not to bother specifying DESC because, and I quote, "[it's] allowed for DB2 syntax compatibility, although indexes are always created in ascending order". As I've just checked, though, that changed in 8i, where DESC now causes the index to become, effectively, a function-based index.

[snip]

>
> To return to the topic of "when should we rebuild an index?" (and I'm
> obviously not as technically expert as most of the people posting here,
> so I'm quite prepared to be told that I'm talking rubbish)...
>
> The impression I'm getting is that the answer is the same as almost
> anything else to do with tuning in Oracle - "It depends".

Yes, but that's a meaningless statement. Since everything quite obviously depends on everything else, there is no actual information content in the "it depends" school of thought. The hard part is (as you are about to say) working out *what* it depends on...

> It depends on
> what DML operations are being carried out, what queries are being
> performed, etc. So, it all comes down to individual cases.

But that is also a largely meaningless statement. It again means "we can't work out general rules, so work it out for yourself". I contend that there *are* general rules. Actually, just one general rule: don't rebuild. But there will be exceptions. Those exceptions can be identified when queries start slowing down, or execution plans start changing and an index is no longer used.

Which isn't *very* different from what you said, but it's nuanced differently. Instead of "We can't tell. Go case by case", I'm saying "We can tell that it is most unlikely most of the time for a rebuild to be worthwhile. If you never rebuilt an index in your life, you will mostly be fine. But you should keep your eyes peeled for the odd index here and there that refuses to obey the rules". Rather a different sense, and a very different outcome.

> In our case, (a merchandising application) the proof of the value of
> rebuilding some indexes comes from the fact that the performance of
> queries on the underlying tables steadily degrades unless the indexes
> are rebuilt regularly (daily in some cases).

Well, you will forgive me, but I find this hard to believe from mere anecdote. There are a lot of anecdotes floating around claiming 60%, 70% even 90% performance boosts from a rebuild. Strangely, there are never any figures, spool files or just plain evidence to back the claims up.

I'm not saying it's not true (you obviously know your application better than I do), but I would like to see some hard evidence (which, if you do this for some indexes on a daily basis, should not be too hard to come up with, surely).

The evidence would ideally consist of something like this:

set timing on
issue some SQL
alter index rebuild
issue same SQL as before

And then we can compare the timing before and after.

> The indexes concerned show
> a significant reduction in size after rebuild (and some even show a
> reduction in height!). This seems to affect the total I/O involved in a
> query. I presume that the underlying DML operations leave us with
> sparsely-populated index blocks after a while, thus forcing range-scans
> to visit more blocks than in the rebuilt index.

Yup. That can certainly happen. And then I think we might get into a bigger discussion. My suspicion is (and it's only a suspicion, so has no more validity than mere performance-gains-claimed anecdote) that if a rebuild of an index really does need doing on a daily basis to maintain performance then there's a more fundamental design flaw that's being exposed. But that's a big topic best left for concrete situations, I think.

> Another thing that experience of our application shows is that we are
> in the happy position that re-building all the indexes doesn't do any
> harm - nothing performs worse afterwards.

See, again, I have a problem with a statement like that. Where's the evidence that nothing performs worse afterwards? If you have *that* evidence to hand (which presumably you collected before embarking on a universal rebuild exercise), where's the evidence of benefit?

I have major qualms about flooding a buffer cache, performing huge quanities of locking, huger quantities of disk I/O, generating vast quantities of redo and archived redo, clobbering my undo segments, and stressing DBWR and LGWR to death without some solid figures to back up the statement that none of that does harm.

And I have even bigger qualms about making such a statement as though it had any applicability to any situation other than your own very specific one.

> I'm trying to do the same sort of things that many respondents here are
> doing - work out whether and how often to re-build an index that I
> don't know much about (eg when a new index is added to the
> application). The difference is that I'm approaching it from the
> position where I already know that some indexes need regular re-build.

If you know, you will have evidence, right? I would really like to see that evidence, because I have *never* seen any posted by those that claim huge performance benefits (I'm not accusing you of anything, just saying the claims and the justification of the claims are never on the same web page at the same time).

> It seems that there's no substitute for experience - and on an
> index-by-index basis. To that end, I've computed statistics (and used
> VALIDATE STRUCTURE) to obtain and record stats on all the indexes in
> the application. I certainly couldn't find anything in the results that
> would distinguish the indexes that I "knew" to be problematic. So, now
> I'm looking at recording the same sort of information over time to see
> if the statistics change in a way that I can use. I'd certainly prefer
> to re-build an index when it really needs it rather than on some
> arbitrary schedule.

I think that's the Holy Grail, isn't it? It's good that you're searching for it, and not merely supping from the false Grails that, er, certain authors have proffered over time.

But we, the Knights that say "Ni!", believe that a nice little herb garden is probably more useful than an index rebuild in about 95% of cases.

Something like that anyway!

Regards
HJR Received on Wed May 12 2004 - 15:33:33 CDT