Re: rebuild index

"Andrea" <netsecurity_at_tiscali.it> wrote in message news:1160119227.159630.257360_at_c28g2000cwb.googlegroups.com...
>> Likely a complete waste of time and CPU. Rebuilding indexes is
>> something you should only do when you have metrics supporting the
>> fact that there is a need to do so.
>>
>> One way to determine whether an index requires rebuilding is to
>> use the built in SYS_OP_LBID function. I have a demo of its usage
>> in Morgan's Library under Undocumented Oracle and there is some
>> documentation by Jonathan Lewis you can find if you google for
>> the function by name.
>> --
>
> For determine if an index requires rebuild i use this method:
>
> analyze index IDXNAME validate_structure;
>
> and after i verify INDEX_STATS table for result (LF_ROWS and
> DEL_LF_ROWS), if the ratio (DEL/LF) is more then 20% i rebuild index.
>

The questions you have to ask yourself when you suggest this approach are:

    How is the OP supposed to identify the indexes that     need this treatment in the first case

    Does the OP have a time when he can lock the tables     that he is supposed to validate indexes for

    Can the OP afford the resources that go into an analyze     of a large index

    Does the timing of this activity make any significant difference     to the results

    If the test shows that several indexes "need" to be rebuilt, how     can the OP schedule those rebuilds so that other processes do     not crash arbitrarily with Oracle error ora-01410

    How many indexes do you rebuild, and how regularly, using     this approach - and did you take any steps to demonstrate     that the performance benefit was worth the risk and effort.

There are cases where it does make sense to rebuild a few indexes for performance reasons. But running on auto-pilot with a "magic number" script is rarely cost-effective, risk-free, or thorough.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html