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?
/Tuve
Printable View
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?
/Tuve
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?
/Tuve
If there have been a bulk deletes from a table, then it's necessary to rebuild the indexes.
tuvham why it would take forever?
Unfortunately as far as I know this is the only way!
Pando,
I think he is assuming you MANUALLY have to validate each index and then check the stats on them.
I haven't tried it, but you could just write a script to validate and check each index and spool to a file or something.
- Magnus
yea just use a dynamic sql 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...
Hi Jeff,
If you publish this under "Oracle Scripts", it will benefit everyone.
Baliga