This content has been marked as final. Show 17 replies
For the great majority of indexes an automatic weekly rebuild would be a waste of computer resources.
There are some cases, ususally in my experience, involving indexes built on columns whose value change frequently after creation where a rebuild will free up significant space; however, even in cases like this the time period is likely to be months, quarters, or even yearly rather than weekly.
You are highly unlikely to see any measurable, repeatable performance improvement from index rebuilds.
HTH -- Mark D Powell --
Instead of weekly rebuild a weekly analyze may make more sense.
Yeah, you are right. It doesn't make much sense to rebuild indexes weekly.
With a SR oracle support is also suggesting (in this case) to do periodic rebuilds but they didn't specify any time period.
But in case if we do not rebuild indexes for very long time, could there be performance degradations or it just concerns with [disk]space only?
Besides that you may be facing a leaf split overhead, which may slowdown your processes.
I may suggest using ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE / SELECT * INDEX_STATS, and DBMS_SPACE.SPACE_USAGE to get a better idea of your index health.
Richard Foot has very detailed material regarding indexes, you may see his blog http://richardfoote.wordpress.com/
If you go for the rebuild option (my DBA has me do one every couple of months or so) be sure - if you have the option / luxury - of turning off archivelog mode. I spent every 5 or ten minutes having to move the logs that were generated and it could have easily been avoided with the old ALTER DATABASE ....command.
Um, Dan? Check the reference manual for the 'NOLOGGING' option of the CREATE INDEX and ALTER INDEX .. REBUILD commands.
No need to change database's archivelog mode.
You might want to consider shrink space instead of rebuild if all you want to do is coalesce partially used blocks.
What does that mean how that works out?
This is 10g database and Index tablespaces are obviously LMT. I didn't get you exactly. Please explain. I just have to suggest about this production db to prod-dba. I should have some facts to present. If you can suggest some links that would be really helpful.
After reading on Asktom and reviewing metalink reply I was a bit confused.
You must follow your vendor guidelines to stay suported, right?
the vendor recommend to have 'job' which will weekly rebuild indexes for few large table.
Oracle Press author John Garmany has a good article titled "Rebuilding Index - when why and how", that can be downloaded from the Oracle web site:
Lt. Col. Garmany says: "The only time you need to rebuild indexes is when the cost to rebuild is less that the performance gained. The first one is obvious. Pack your tables and indexes (rebuild into the soon to be read-only tablespace) tightly and they will stay that way.
The second is much more difficult. First, it is a continuing process since the index will move toward fluff with use. Second, there is the cost of rebuilding the index AND the cost of the additional redo as the index changes. There is only one method to determine is rebuilding an indexes benefits your database, testing."
Also I have my notes here:
See Oracle MetaLink note 122008.1 for the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Oracle's index rebuilding guidelines appear in Metalink note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:
“When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.
It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.”
The note also says that the index rebuilding criteria has changed since the advent of Oracle9i, where a blevel > 4 was a good threshold. It also notes that the pct_deleted column in index_stats from “alter index xxx validate structure” provides a guideline for index rebuilding candidates:
“Prior to 9i, if the BLEVEL is more than 4, it is recommended to rebuild the index. . .
The PCT_DELETED column shows what percent of leaf entries (index entries) have been deleted and remain unfilled.
The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding.”
Hope this helps. . .
Donald K. Burleson
Oracle Press author
Author of "Oracle Tuning: The Definitive Reference":
Also I have my notes here:If you check Metalink, you will find that both the notes you mention are no longer available.
See Oracle MetaLink note 122008.1 for the officially
authorized script to detect indexes that benefit from
rebuilding. This script detects indexes for
rebuilding using these rules: Rebuild the index when
these conditions are true:
- deleted entries represent 20% or more of the
- the index depth is more then 4 levels.
Oracle's index rebuilding guidelines appear in
Metalink note 77574.1 (dated April 2007) recommends
that indexes be periodically examined to see if they
are candidates for an index rebuild:
I read them Re: Leaf nodes and Blevel in an index and used the Metalink feedback forms to point out how inappropriate the content was - so they've been pulled.
I get it. Thanks a ton for enlightenment(s). You have made everything clear. Still your notes keeps pressing upon fact that index rebuilding should be based upon 'conditions' not 'time'. So we can design scripts in that way.
I do not have anything to do with putting on or off any prod jobs. Just to suggest what seems good to me. Vendor 'recommends' only for space concern, not performance, thats a different matter. Please dont mind.
We hope that Oracle correct their content with valid and up-to-date information; you, Richard Foote and Tom Kyte have researched and published excellent material about indexes, that should be considered to reform those notes.
PS: I wonder why Oracle took too long to take action on this...
But nothing was updated, nor made up-to-date, just removed?
We hope that Oracle correct their content with valid and up-to-date information
I wonder why they no longer give any advice at all on this popular topic?
I wonder why Oracle took too long to take action on this...
Sadly, if his vendors says that they must do something to stay supported, he may not have any choice.
I've seen loads of unnecessary rebuilds done because a vendor threatended to desupport the customer did they did not comply . . . .
But nothing was updated, nor made up-to-date, justYep, just removed... that's why we hope
I wonder why they no longer give any advice at all onThat's because business software (ERPs, CRMs, BI, etc) vendors mostly design the product for one database-abstract-layer, sometimes with slight modifications to fit particular DB infrastructure. Some procedures are valid for specific DB, same are not valid for other DB, but they have generalized the relationship among Application and DB, in a one-solution fits all.
this popular topic?
Sadly, if his vendors says that they must do
something to stay supported, he may not have any
I've seen loads of unnecessary rebuilds done because
a vendor threatended to desupport the customer did
they did not comply . . . .
It's logical, seems they are focused on business processes, not Application performance nor exploit new or exclusive DB features, that would fork their development tree without great returns.