One more thing @PAVB, if orignal poster's appl deletes 36 million records per week (though not practical) then i would guess/expect it would leave many free blocks for re-use. I suspect if oracle is using blocks efficiently in his case, either due to wrong settings in his environment or oracle is genuinely failing to re-use blocks or he is using IOT. IMHO IOT not re-using index blocks though we do delete range of sequence numbers (first key of IOT) which are older than some date.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
This whole thing was discussed a couple of days ago... please research.
Index blocks are re-used ONLY in they get empty, if you have a single active key sitting in the middle of the block and you are inserting keys that do not belong to the block, block is not reused.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
I know that.. PAVB, i remember pando and myself making a point of block being re-used if all keys are deleted (wrt sequence/date).. unlike a set/range of blocks.. (i hope you remember).
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
when did i say that for a index with sequence/date as first key, if any block has even a single entry, then its candidate for re-use.. can you point where and when did i mention this?
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Reply With Quote
Bookmarks