Re: rebuild indexes

Okay,
I understand the question now.

All the values in INDEX_STATS are purely dynamic. Oracle calculates them on the fly as it reads through the index. Consequently there is no concept of 're-initiliasing' the figure, it just drops out of whatever the index contains at the time.

Since it is

    index height plus (average rows per key / 2)

then the index height MIGHT drop by 1 (or even 2) when you rebuild the index, but for 'low grade' non-unique indexes the (average rows per key / 2) is likely to be the most significant bit, and that won't change because it is purely a measure of the actual data

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

David Spaisman wrote in message
<7ltbv0$m11$1_at_mailint03.im.hou.compaq.com>...
>Jonathan:
>
>Thanks for the reply.
>
>
>My question was whether or not does blks_gets_per_key become initialized
>when the index is either rebuilt or recreated?
>
>Apparently not but I just wanted to confirm this.
>
>Thanks.
>
>David Spaisman
>Jonathan Lewis wrote in message
><931118614.7279.0.nnrp-12.9e984b29_at_news.demon.co.uk>...
>>
>>As I wrote a couple of weeks ago:
>>blks_gets_per_key is roughly half the average number
>>of rows per key plus a few. Specifically it is:
>> (index height + (number of leaf rows /(2 * number of distinct keys))
>>
>>Example:
>> 10,000 rows in table
>> 100 different key values
>> Index height 3
>>
>>bgpa will be:
>> 3 + (10,000 / (2 * 100)) = 53.
>>
>>It has no bearing on the efficient use of storage,
>>it just just a (fairly poor) measure of whether the
>>index should exist or not.
>>
>>--
>>
>>Jonathan Lewis
>>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>>
>>David Spaisman wrote in message
>><7lislb$c88$1_at_mailint03.im.hou.compaq.com>...
>>>Hello:
>>>
>>>I have been told that the two parameters -- blks_gets_per_access and
>>>pct_ued -- were key indicators on whether or not an index should be
>>rebuilt.
>>>
>>
>>
>>
>
>
Received on Tue Jul 06 1999 - 16:12:03 CDT