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

Giganews Newsgroups
Subject: Re: Rebuild of indexes and 'Sweeper' referred in Ask tom site
Posted by:  hpuxrac (johnbhurl…
Date: Thu, 29 Nov 2007

On Nov 29, 9:35 am, nirav <shiva.…> 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.


In response to

Rebuild of indexes and 'Sweeper' referred in Ask tom site posted by nirav on Thu, 29 Nov 2007