Re: When should one rebuild an index?

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

Hmmm, let me review OP's message again. I still don't see the part he asked about whether an index is eligible for rebuilding. You are welcome to quote his question here.

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

ANALYZE...VALIDATE is deprecaded? By whom and in what version? I don't know what your definition of "meaningless" is here. How could something be "meaningless" yet provides a specific piece of information.

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

It seems we have been through this before. How could something be relevant yet useless? There is not a single system stat alone that can tell if performance is ok and what's wrong with it, nor are they meant to. Are they all 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.
>

No one is saying that cannot be the case - a special case I might add. Received on Sat Dec 27 2008 - 19:41:16 CST