Re: When should one rebuild an index?

"hpuxrac" <johnbhurley_at_sbcglobal.net> wrote in message news:ff677f90-7ac5-463a-aa2c-bafbe85266a5_at_s9g2000prg.googlegroups.com... On Dec 28, 7:08 pm, "Bob Jones" <em..._at_me.not> wrote:

snip

>> Index structure and space usage are meaningless in determining index
>> rebuild? So in your opinion what is meaningful?

>Bob it is "getting to be" fairly well accepted in oracle dba land that
>for the most part the need to rebuild indexes on a regular scheduled
>ongoing basis is way oversold.

True, but that's not what we are debating here. We are talking about whether INDEX_STATS is useful in determining index rebuild.

>That being said, many shops have available cpu and time and schedules
>that allow scheduled rebuilds to not interfere with business
>priorities.

>One can build test cases which either demonstrate that "little to no
>gains" to applications ( and important business activities ) are
>gained when doing certain rebuilds or "significant gains" are made.

Yes, it is very application dependent. Index rebuilds often yield different results in different situations.
Back in the days when I was a developer, INDEX_STATS and other info were routinely used to find potential index rebuilds. There were disagreements on when to rebuild, but none thought INDEX_STATS was useless.

>Cary Millsap has a good definition that is relevant to meaningful if
>you want to read his book on Optimizing Oracle Performance. To badly
>paraphrase it the basic concept is when the net gain to the business
>of performing the performance tuning ( which an index rebuild is one
>potential type of a performance tuning method ( or at least an attempt
>at one )) is not much more than the cost of doing the performance
>tuning ... that's when to stop.

>The guy you are posting back and forth with just loves to keep posting
>and will try to make strange points unrelated to your replies.

I know. Received on Mon Dec 29 2008 - 20:43:52 CST