Re: When should one rebuild an index?

From: DA Morgan <>
Date: Sun, 28 Dec 2008 13:16:08 -0800
Message-ID: <>

Robert Klemme wrote:
> On 27.12.2008 23:53, Bob Jones wrote:

>> "DA Morgan" <> wrote in message 
>>> Bob Jones wrote:
>>>> "DA Morgan" <> wrote in message 
>>>>> Bob Jones wrote:
>>>>>> <> wrote in message 
>>>>>>> Hi
>>>>>>> We can use
>>>>>>> command on the affected indexes - each invocation of this command
>>>>>>> creates a single row in the INDEX_STATS view.
>>>>>>> Thus determining the indexes which are good candidates for 
>>>>>>> rebuilding.
>>>>>>> BUT my doubt  is that if,
>>>>>>> we use dbms_stats to gather the statstistics for CBO using cascade
>>>>>>> option. Then if
>>>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE  is ran , that what will be
>>>>>>> fate of the stattistics gathered using dbms_stat for index( cascade
>>>>>>> option).
>>>>>>> Also, analyze command is deprecated now. So is there any other 
>>>>>>> way to
>>>>>>> find out the indexes which needs rebuilding.?
>>>>>> INDEX_STATS is populated by ANALYZE only. Your CBO data collected 
>>>>>> by DBMS_STATS are safe.
>>>>> The CBO data is safe but what you are suggesting as criteria 
>>>>> irrelevant.
>>>> What criteria?
>>> I will state it clearly.
>>> The data collected by ANALYZE does not lead to a conclusion as to
>>> whether an index should be rebuilt. In other words ... the fact that
>>> CBO data is not destroyed is irrelevant. Time is being spent, CPU and
>>> I/O wasted, producing a metric that is not a relevant criteria.
>> It doesn't look like OP has reached any conclusion yet. He was merely 
>> asking a question. I think my response was relevant to his question. 
>> Whether he should rebuild indexes based on the stats and other factors 
>> is not my call.

> IMHO your notion of relevance here is a bit too formal - while part of
> the OP's question was indeed whether DBMS_STATS data is safe when doing
> ANALYZE the more important question he asked was how to determine
> whether an index is eligible for rebuilding. If the answer is "not by
> using ANALYZE" it is completely irrelevant whether or not ANALYZE
> destroys DBMS_STATS data.
>>> This is not different from the same mythologist promoting Buffer 
>>> Cache Hit Ratios. Yes you can generate a value. A numeric value. But 
>>> valid
>>> conclusions can not be drawn from that numeric value. Only, perhaps,
>>> about the person that suggested making a decision based upon it.
>> Not different in the way BCHR is also irrelevant to performance and it 
>> is just a number? That's fascinating.

> Well, I guess Daniel means that the output of ANALYZE (which is
> deprecated btw., this has also been mentioned already) is meaningless
> without further information much the same way as a high or low BCHR is
> meaningless without further info. A high BCHR can indicate that your
> application is doing too much useless work (wasting CPU cycles doing
> table scans in buffer cache) and a low BHCR can indicate that your app
> does not have proper indexes (and thusly needs to do FTS for objects
> larger than BC). BCHR of course is not irrelevant for performance
> because a change most likely correlates with changed performance - but
> it does not help you find out whether performance is ok or not and
> what's wrong in case not. So it's relevant but useless.
> Whatever numbers you get from ANALYZE - they are only a snapshot of the
> current index structure. Without further information about the data,
> change nature and frequency it is pretty useless because your perfectly
> rebuilt index might be back to before in less time than you needed for
> the rebuild rendering rebuilding of this index useless to harmful.
>> Perhaps, instead of looking at stats, we just need an Ace Director to 
>> orchestrate DBAs what to use or not to use in all situations. 

> Well, delegation is a valid approach - there are even people getting
> paid for doing consultancy... ;-)
> Cheers
> robert

Thakn you Robert ... very well said.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Dec 28 2008 - 15:16:08 CST

Original text of this message