Re: Index management

Howard J. Rogers wrote:
> *Howard J. Rogers wrote:
>
> .....
> Incidentally, and just for fun, what happens to my "bloated", and
> "right-hand skewed" index if I rebuild it (immediately after the
> test
> above finished)?
>
> SQL> alter index invidx rebuild;
> Index altered.
>
> SQL> analyze table invoices compute statistics;
> Table analyzed.
>
> SQL> select leaf_blocks from dba_indexes where index_name='INVIDX';
>
> LEAF_BLOCKS
> -----------
> 444
>
> So if I DON'T rebuild, the index has 422 leaf nodes. And if I DO
> rebuild
> this wicked, skewed bloated monstrosity of an index, it has 444 leaf
> nodes.
>
> And no, I'm not making that up. The index is *bigger* after the
> rebuild
> than before. Surprised? I was, actually. But there you go. That
> might
> give the "I have some spare CPU cycles so why not use them because
> it
> can't do any harm" school of index management pause for thought.
>
> *

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. NB: my test was unrealistic in that it used quite large key values because I wanted to force some changes in HEIGHT. As a result, my indexes would have had an unusually large proportion of branch blocks.

Fortunately, most real-world scenarios don't insert in decreasing key order, so most indexes don't tend to get skewed. Lots of applications do insert in increasing key order, and so the resulting indexes can be very efficient (until we start deleting out of the underlying table).

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". It depends on what DML operations are being carried out, what queries are being performed, etc. So, it all comes down to individual cases.

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). 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.

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.

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.

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.

--
ademmerson
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message625756.html