Re: When should one rebuild an index?

From: Bob Jones <email_at_me.not>
Date: Sat, 27 Dec 2008 19:41:16 -0600
Message-ID: <XOA5l.9804$>

> 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

Original text of this message