Re: Index rebuilds

Neil Emery wrote:

> In the "olden" (or should that be golden?) days we were all recommended to
> regularly export and import
> tables where a large number of inserts/deletes occurred to 'tidy' them up.
> Am i correct in saying that this is no longer required with oracle 7/8/9 ?
>
> TIA
>
> Neil

I'm a bit confused about your post. Your title asks about index rebuilds; your content talks about tidying up *tables*.

For indexes, a rebuild is almost never actually necessary.

For tables, yes... a re-organisation of a table that has been subject to many deletes is probably an occasional good idea. The problem is 'inflated high water mark syndrome': you insert a million rows. High Water Mark is off in the wild blue yonder. You delete 900,000 rows. High Water Mark is *still* off in the wild blue yonder, causing a full table scan to trawl through the empty space previously occupied by the former 900,000 rows. A judicious bit of re-organisation will bring the HWM back to reality, and full table scans will speed up.

In version 8i and above, a re-organisation can be better effected with an 'alter table X move' statement, rather than a cumbersome export and import. However, a move invalidates all indexes on the table, and therefore those indexes would all have to be rebuilt (thus apparently contradicting my first answer!). With import, of course indexes need to be rebuilt as well, but import by default does that for you, so you tend to forget that it's happening.

Point is, any form of re-organisation of tables is expensive in terms of data availability, I/Os, index rebuilds and table locking issues. So avoid wherever possible: if you never, ever do full table scans against a table, then who cares if the HWM is located near Timbuctu? The problem then is only a matter of 'wasted' disk space... which you might, or might not, care about. If you were about to insert 800,000 new rows into that earlier table, the pain of a re-organisation would be entirely unnecessary, because your 'inflated' HWM is about to get an awful lot more realistic without any re-organisation effort on your part.

So it all comes down to this rule of thumb: there are no rules of thumb. You re-organise a table when it would be of benefit to re-organise it. And to know when that is the case, you need to know your application, the way it accesses data, what will happen to data in the future, data growth habits, and so on.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------