How does one determine when itīs time to rebuild a index?
Is it the clustering factor, leaf blocks or distinct keys that indicates this? Perhaps all of them?
I believe its fragmentation on the leafs that determine this, which you can check in index_stats, after VALIDATE INXDEX command. There should be no more than 10 per cent of deleted rows to existing rows
Well, perhaps that works but there must be a simpler way to determine this. If you have thousands of indexes you cant analyze xxx validate + INDEX_STATS on every single on of them. (well, you CAN technically but itīll take forever).
Anybody got a neat script?
Iīm just thinking of the situation in which you are out of office, on a customer database, and donīt have your premade script to help you. Then you have to make a script to see the indexes that need rebuilding and then you have to run it, which probably takes some time (havenīt tried it but i can imagine). All this only for checking indexes. (think sga,dd,db buffer,tables,sql,parameters and so on)
I remember someone saying that its time to rebuild indexes if the index is bigger than the table, but that is probably a pretty inprecise method.
Maybe the best solution to get rid of the administration effort is to have them all rebuilt automatically on weekends.
Or what do you think?
/Tuve
Send mail to marist89@excite.com with the subject heading of "SCRIPT: rebuild_candidates.sql" and I'll send you a package that I wrote to gather these statistics...
Reply With Quote
Bookmarks