On 29.11.2007 16:25, hpuxrac wrote:
> On Nov 29, 9:35 am, nirav <shiva..._at_gmail.com> wrote:
>> Hi ,
>>
>> I have a question about rebuild of indexes...In Ask Tom site, Tom Kyte
>> says that rebuild is not required in 99% of time...except in situation
>> like 'sweepter'...but I am not getting clarity on what is the
>> 'sweeper' term referring to. Can you please help me understand with
>> an example.
>>
>> Thanks,
>> Nirav
>
> I think Jonathan Lewis also has this covered pretty well so you might
> want to look around at his site and some of the documents he has done.
>
> Basically the current theory is that over the years a misunderstanding
> occurred in many of the oracle dba area and a lot of people put a lot
> of time and effort into implementing jobs to rebuild indexes. Maybe
> some of that came from the mainframe database side where it is also
> common to have jobs that reorg databases and their indexes whether
> they are hierarchical or relational ... but regardless of where it
> came from ... it often is not needed. In fact, it can be counter
> productive if not a waste of time, effort, cpu cycles, etc.
>
> Not sure exactly what the sweeper thing is but in some cases the
> behavior of applications and the sets of changes that they make to
> tables and their indexes ( large batch updates, large batch deletes,
> inserts patterns etc ) can at times cause situations where one wants
> to "consider" index rebuilds.
>
> However sometimes these situations can also be handled by other
> actions. Dropping or disabling indexes before certain batch
> patterns ... etc.
>
> You want to be careful about things with numbers like the 99 thing.
> The more important thing to think about is what processes are most
> important to your business and need some attention to improving them.
> If you are using valuable cpu resources or causing application
> downtime windows while doing index rebuilds that don't need to be
> done ... that's something to think about.
There is also a nice paper about index rebuilding myths debunked. I
can't seem to find it right now but as far as I remember Richard Foote
was the author and he posted the reference here himself. Very worth the
read!
Cheers
robert
Received on Thu Nov 29 2007 - 12:20:28 CST
