Re: When should one rebuild an index?
From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 27 Dec 2008 11:10:16 -0800
Message-ID: <1230405015.761378@bubbleator.drizzle.com>
>> Bob Jones wrote:
>>> <shweta.kaparwan_at_googlemail.com> wrote in message
>>> news:3bddc9d2-d04f-4525-9703-9c74ccd33d9e_at_h20g2000yqn.googlegroups.com...
>>>> Hi
>>>>
>>>> We can use
>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE
>>>> 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.?
>>>>
>>>> Thanks.
>>>>
>>> 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?
Date: Sat, 27 Dec 2008 11:10:16 -0800
Message-ID: <1230405015.761378@bubbleator.drizzle.com>
Bob Jones wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message > news:1230357209.294829_at_bubbleator.drizzle.com...
>> Bob Jones wrote:
>>> <shweta.kaparwan_at_googlemail.com> wrote in message
>>> news:3bddc9d2-d04f-4525-9703-9c74ccd33d9e_at_h20g2000yqn.googlegroups.com...
>>>> Hi
>>>>
>>>> We can use
>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE
>>>> 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.?
>>>>
>>>> Thanks.
>>>>
>>> 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.
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.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sat Dec 27 2008 - 13:10:16 CST