Re: Index rebuilds

On Thu, 09 Oct 2003 06:12:37 +1000, Howard J. Rogers wrote:

<A LOT OF GOOD INFO>

The only thing that Howard forgot to mention is :

if your indexed column is based on an oracle sequence AND you do a lot of deletions of rows THEN

you might at some point have to rebuild the index because the sequence is generating an increasing value each time so a row (or rows) that have been deleted will NEVER be reused because the indexed column, generated by the sequence, is always a higher value.

Of course, if the deleted row is in the current index block (the one where ALL the inserts are taking place) then the deleted index entry can be reused, but only while that block remains current.

On the other hand, you could use a reverse index for this situation and spread the load over all the index blocks and avoid having to rebuild. Than again, this causes the clustering factor of the index to change which causes the cost based optimser to change its access path. :o)

Cheers,
Norm.

-- 
Delete the obvious bit from my email address to reply by email.