Re: Rebuild of indexes and 'Sweeper' referred in Ask tom site

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. Received on Thu Nov 29 2007 - 09:25:40 CST