Re: rebuild index

Jonathan Lewis wrote:
> "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.
>

i agree, thanks very much Jonathan. Received on Fri Oct 06 2006 - 02:37:20 CDT