This content has been marked as final. Show 23 replies
When you rebuild an index, the physical effect is to reduce the size of the index (especially the leaf block count). This may result in three possible benefits:
The optimiser calculations may produce a smaller value for the cost of using the index, so the optimiser may use the index in more execution plans.
Typical queries that use the index may have to visit fewer index blocks, and therefore run more efficiently.
Because the index is smaller, it may become a better survivor in the buffer cache LRU chain, so that blocks from the index are found in the buffer more frequently, and fewer blocks from other objects are flushed to allow index blocks to be reloaded. If this occurs, system-level I/O is reduced, resulting in a possible performance gain for everyone.
> When you rebuild an index, the physical effect is to
reduce the size of the index (especially the leaf
block count). This may result in three possible
The optimiser calculations may produce a smaller
value for the cost of using the index, so the
optimiser may use the index in more execution plans.
Typical queries that use the index may have to visit
fewer index blocks, and therefore run more
Because the index is smaller, it may become a better
survivor in the buffer cache LRU chain, so that
blocks from the index are found in the buffer more
frequently, and fewer blocks from other objects are
flushed to allow index blocks to be reloaded. If this
occurs, system-level I/O is reduced, resulting in a
possible performance gain for everyone.
You're right, especially on the words usage of "may", "if" and "possible". The improvement is maybe less than the rebuilding cost of indexes.
That's why I wouldn't advise to rebuild index every day by automatic procedure. I would prefer to be sure about the target to touch.
One of the headaches with the rebuild is: if the leaf block issue is created by the pattern of DML then (and especially during DML) the database is working harder in an effort to restore the 'inefficient' leaf pattern. SO you need to rebuild because the normal state is not efficient for some queries.
This is well documented -see the referenced presentation from Mr. Foote.
It would be much better to determine the actual cause of the problem and make a rational decision. For example, perhaps a materialized view would be better than maintaining that specific index. Or perhaps a FBI or reverse key index would be better.
As it is, rebuilding indexes periodically make external consultants very happy - while the DBA is off doing rebuilds, the consultant can be billing for interesting DBA work. (And THAT is the justification for the Diagnostics Pack in 9i and higher!)
After reading some documents and links provided in this thread i can understand that rebuilding index in a regular interval is a bad practice.
The question is when should we rebuild a index??
I could not come to a conclusion. I read the below points from some documents..
select substr(segment_name,1,50), segment_type, extents, bytesIf the above query returns any row ????
where extents > 5
and segment_type = 'INDEX'
and owner = user
order by extents desc;
2. select blevel from dba_indexes where index_name=<index name>
If blevel is more than 3 ???
3. select lf_rows, del_lf_rows from index_stats where name= <Index name>
Looking at ratio of del_lf_rows and lf_rows???
Or index rebuilding is not required at all???
Please share your ideas on this.
As previously mentioned, go to Google, do a search on "index internals richard foote", and read the paper. It explains in detail, why index rebullds are almost never needed, and the few cases where they may be beneficial.
None of the three metrics listed above, (lf_rows/del_lf_rows, blevel, number of extents) are valid metrics to determine if an index should be rebuilt.
It's good to see that my presentation is still getting read and helping folks with questions such as these.
It still amazes me though how many DBAs still consider rebuilding indexes every x number of days as an essential part of their job role.
I would love to sit next to a DBA one day who after very careful analysis spots one of those evil indexes that has a level greater than 3, then rebuilds the thing while watching the hour glass as redo logs churn out log after log, only to find that the index still has a level greater than 3 afterwards. I of course would simply smile and ask if they're ready to try it again ...
Then of course if the index were to reduce in height, you do have to wonder just how lightening fast the unique index scan will suddenly become because the index scan would have to read many leaf blocks, usually many tens of thousands of rows for any benefit to be noticeable to the application user. However most of these rebuilding rules of thumbs don't consider the actual benefit arising from such a rebuild.
I'll hopefully be presenting a two day Oracle University seminar on all things Oracle Index Internals in some countries in EMEA early next year. Looks like it might be beneficial for a number of folk in this thread to attend !!
As far as I am concerned, there are two cases where an index re-build is required.
1. After you have done an ALTER TABLE t MOVE for some valid reason
2. After you have done ALTER INDEX i UNUSABLE and a massive dataload.
In both these cases an index re-build is mandatory.
There used to be a third case. If you have an index on, for example, a sequence generated number as a primary key. Another column in the table is a flag indicating record processed. Every x period of time you go out an delete all of the records where the processed flag is Y. Most, but not all, of the older (i.e lower sequence number) records are deleted, leaving many index leaf blocks with only one or two index entries in them. Since the new records being added (i.e. higher sequence numbers) cannot use these blocks because the values are wrong, a re-build used to be a good idea. Since (I believe) 9i, you can now do ALTER INDEX i COALESCE to compact those underfilled blocks into a much smaller number of blocks, so a re-build is no longer required.