index rebuilds

[hacketta1]

Index with first column populated by sequence and the application is such that data is deleted for old generated sequence numbers

Is it this straight forward? My understanding is that if the deletion criteria meant that after the delete there was loads of totally empty blocks a rebuild isn't necessary. The blocks are empty, are not part of the index structure, and therefore Oracle can and will reuse these blocks. On the otherhand, a "scattered" delete that leaves a load of blocks with very few rows in would be a rebuild candidate. The partially empty blocks caused by the delete are still part of the index but the sequential value means they won't be re-used.

[abhaysk]

Originally posted by hacketta1
Is it this straight forward? My understanding is that if the deletion criteria meant that after the delete there was loads of totally empty blocks a rebuild isn't necessary. The blocks are empty, are not part of the index structure, and therefore Oracle can and will reuse these blocks. On the otherhand, a "scattered" delete that leaves a load of blocks with very few rows in would be a rebuild candidate. The partially empty blocks caused by the delete are still part of the index but the sequential value means they won't be re-used.

That isnt really straight forward.. I should have still made it clear..

** and the deletes are so, not all old generated numbers are deleted and are kept intermittently by other key value.. which would mean that blocks are/may partially filled & will not be a candidate for reuse..


Abhay.

[slimdave]

In that situation you should be looking at a reverse-key index anyway (to avoid contention on inserts), and the problem would go away.

[OracleDoc]

Originally posted by abhaysk
[B]OracleDoc,

If my memory is good, then i think you had in your systems to rebuild the indexes every week?...

You also said that the practice of it was a ** "history" **..

Who me? Ohhh h3ll no I wouldn't rebuild them every week. I'm a strong advocate of "if it aint broke don't fix it"