Re: When should one rebuild an index?

From: Bob Jones <email_at_me.not>
Date: Sun, 28 Dec 2008 18:52:29 -0600
Message-ID: <%6V5l.6952$8_3.3563@flpi147.ffdc.sbc.com>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1230501008.751907_at_bubbleator.drizzle.com...
> Bob Jones wrote:

>> "DA Morgan" <damorgan_at_psoug.org> wrote in message 
>> news:1230425754.597314_at_bubbleator.drizzle.com...
>>> Bob Jones wrote:
>>>
>>>> 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.
>>> Cute Bob. Really cute. The advice you are hearing from Oakies, from
>>> Aces, from internal Oracle resources is the same. If you wish to ignore
>>> it that is certainly your choice.
>>>
>>> None of us make a dollar if you take our advice. None of us lose a wink
>>> of sleep if you ignore it. The simple fact is that sooner or later your
>>> employer will contact one of us to bail the bilge. Whether you will 
>>> still be there when that happens is irrelevant tous. But a cautionary
>>> note to you and others who think attitude trumps aptitude. There is a
>>> reason why Oracle is being so successful selling the "you need fewer
>>> DBAs" mantra. CIOs and CTOs see too many dollars wasted on people that
>>> don't read. Too many dollars wasted on people that go to classes and
>>> still keep doing things the old way. Too many dollars wasted on people
>>> that think they know better than Tom Kyte (still not using bind
>>> variables), Richard Foote (still rebuilding indexes), Jonathan Lewis
>>> (still tuning based on BHCR), etc.
>>>
>>
>> Hmmm, I have been here well over 10 years and still happily employed. In 
>> fact, our company is still doing very well in this down economy.
>> There is nothing wrong with showing attitude or aptitude, just not by 
>> throwing people's names around though.
>>
>>> What I am telling you in simple terms is that you can run ANALYZE
>>> INDEX from now until you are a member of the gray-hair-no-hair crowd
>>> like me and you will not once see anything that will clearly indicate
>>> that your database will benefit from an index rebuild. It is the wrong
>>> tool. You can start figuring that out for yourself here if you wish:
>>> http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4005.htm#SQLRF01105.
>>>
>>
>> I see no part of this doc suggesting ANALYZE being useless in determining 
>> index rebuild. Where?
>

> You also see no part suggesting it is useful either do you? Are you
> able to acknowledge that fact?

>

So why did you point out the doc if it does not say neither?

> But here: Knock yourself out explaining whether this index should or
> should not be rebuilt based on the information generated by ANALYZE.

>

> SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks,
> btree_space, used_space
> 2 FROM index_stats
> 3 WHERE name = 'IX_TEST';

>

> no rows selected

>

> SQL> ANALYZE INDEX ix_test VALIDATE STRUCTURE;

>

> Index analyzed.

>

> SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks,
> btree_space, used_space
> 2 FROM index_stats
> 3 WHERE name = 'IX_TEST';

>


>     HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS 

> BTREE_SPACE USED_SPACE
> ---------- ---------- ---------- ---------- ---------- ----------
> ----------- ----------
> 2 128 20863 97 96 1 784032
> 768604

>

> SQL> delete from test;

>

> 20863 rows deleted.

>

> SQL> commit;

>

> SQL> ANALYZE INDEX ix_test VALIDATE STRUCTURE;

>

> Index analyzed.

>

> SQL> SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks,
> btree_space, used_space
> 2 FROM index_stats
> 3 WHERE name = 'IX_TEST';

>


>     HEIGHT     BLOCKS    LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS 

> BTREE_SPACE USED_SPACE
> ---------- ---------- ---------- ---------- ---------- ----------
> ----------- ----------
> 2 128 20863 97 96 1 784032
> 768604

>

> SQL>

>

Only less than half of the columns here but that's ok.

> As you can not the obvious inference is that ANALYZE does not, in and
> of itself, produce information of value in determining whether there
> will be a benefit from an index rebuild.
>

I am not surprised you reached this conclusion based on only the example above. Received on Sun Dec 28 2008 - 18:52:29 CST