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: Jonathan Lewis <>
Date: Fri, 30 Nov 2007 09:06:00 -0000
Message-ID: <>

"nirav" <> wrote in message
> 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 can't remember if Richard uses the term "sweeper" in his paper, so here's a simple example of the idea.

One process inserts data into a "queueing" table, using a sequence number to indicate the order of data arrival.

A second process picks up the data one row at a time in order of arrival (i.e. sequence number), does something with it and deletes it from the table - so the quantity of data in the table is always pretty small.

For some reason, though, some of the rows cannot be processed and are left in the table. This can lead to cases where the index could end up with a list of values like:

1, 3123, 8346, 14543, 27235, 44001, 44002, 44003, 44004 ...

Because of the way Oracle re-uses space in indexes, this can leave you with an index where a lot of blocks at the left hand end of the index that have just one or two entries per blocks (the old, bad, data) and just a couple of packed blocks at the right hand end of the index.

If your code has to walk through all those nearly empty blocks all the time, the process gets slower and slower as time passes. The simple mechanical solution is to coalesce the index regularly (rather than doing a rebuild - although you may want to do a rebuild the very first time you discover you have the problem).


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Fri Nov 30 2007 - 03:06:00 CST

Original text of this message