Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Rebuild of indexes and 'Sweeper' referred in Ask tom site

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

From: Robert Klemme <>
Date: Thu, 29 Nov 2007 19:20:28 +0100
Message-ID: <>

On 29.11.2007 16:25, hpuxrac wrote:
> On Nov 29, 9:35 am, nirav <> 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!


        robert Received on Thu Nov 29 2007 - 12:20:28 CST

Original text of this message