There’s been an interesting discussion on the OTN Database forum about a specific issue that someone saw as a result of running a daily bulk delete/ bulk insert cycle on a specific table.
The nature of the data, and the way they were working, resulted in a fairly serious collapse in the quality of the indexes on that table.
I made a number of points in the conversation that I’ve probably not published elsewhere, so I thought I’d post a link to the entire thing, rather than trying to collate and rewrite the details.
[Updated May 2009]: This thread has since gone missing from the OTN forum.
Hi Jonathan,
I wonder why did you leave out your first suggestion with a pctfree increase http://forums.oracle.com/forums/post!reply.jspa?messageID=3190417 from your next options http://forums.oracle.com/forums/post!reply.jspa?messageID=3195322 ?
Always thanks for your knowledge sharing.
Comment by Bunditj — January 1, 2009 @ 5:47 pm UTC Jan 1,2009 |
Jonathan,
I have updated the OTN post with one more possible solution. Not sure if that would work as intended. Can you please check if that will work.
Regards,
Sachin
Comment by Sachin — January 2, 2009 @ 9:30 am UTC Jan 2,2009 |
Bunditj,
Simple oversight – although choosing the best pctfree is still something he has to do whichever strategy he adopts from the rest of the list. I’ve added a note about that to the end of the thread.
Sachin,
See my comments on the thread
Comment by Jonathan Lewis — January 2, 2009 @ 10:59 am UTC Jan 2,2009 |
In one of his comments on the thread Charles Hooper (a fellow Oak Table member) listed a few URLs of discussions on the topic of index rebuilds that had appeared on the newsgroup “comp.databases.oracle.server”
This prompted me to run a little search to see what other comments I had made about index rebuilds pre-dating the examples that Charles had found.
I listed a couple of results on the same Forum thread. The advice and examples about the need to understand the data and application are just as relevant as they were when I wrote that note nearly 10 years ago.
Comment by Jonathan Lewis — January 3, 2009 @ 7:20 pm UTC Jan 3,2009 |