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
"nirav" <shivam71_at_gmail.com> wrote in message
news:fc6c9514-98fb-4e77-ab25-fc7f0396a61f_at_e4g2000hsg.googlegroups.com...
> 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).
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Fri Nov 30 2007 - 03:06:00 CST