Re: rebuild indexes

To help determine if your indexes should be rebuilt, try executing an ANALYZE INDEX to gather statistics on the index. This analysis will create a temporary view called INDEX_STATS. Calculate from columns in this view the percentage of leaf node length that is deleted

[DEL_LF_ROWS_LEN / (DEL_LF_ROWS_LEN + LF_ROWS_LEN)] and the percentage of actual leaf nodes deleted

[DEL_LF_ROWS / (DEL_LF_ROWS + LF_ROWS)]. If either of these percentages is excessive (I usually look for 15-20% or higher), then then the index is a good candidate for rebuild. If this index is in a tablespace with a lot of indexes that have extended a lot, you should just drop the index and recreate it. If it is in its own tablespace (or no indexes have extended) then you can get away with using just the rebuild option (make sure to specify the tablespace in the syntax--the newer versions of Oracle have fixed the problem with rebuilding into the user's default tablespace if no tablespace is specified, but I like to include it "just in case"). Hope this helps.

Jeff S

David Spaisman <david.spaisman_at_compaq.com> wrote in message news:7lism5$cjq$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.
>
> My environment for this testing is Oracle 8.0.4.00 on NT 4.0 sp4.
>
> In a test environment, I have rebuild indexes that exceed
> blks_gets_per_access(> 5) .
>
> I would like to clarify my understanding of how this parameter(and
Pct_used)
> from index_stats work.
>
> If I rebuild the index or drop and recreate the table(and then the index)
> this parameter --blks_gets_per_access-- does not change. I thought that if
> the table was rebuilt the parameter would be initialized. That does not
> appear to be the case. In a specifc case, the blk_gets_per_access is 2269
> and it didn't change regardless of whether I rebuilt the index or
> dropped/recreated the table and index. Restarting the datbase/instance
> ddin't change the parameter value as well.
>
> Is this the way this parameter works or did I do something wrong? Is this
> abug possbily?
>
> Thanks.
>
> DAvid Spaisman
>
>
Received on Mon Jul 05 1999 - 03:11:25 CDT