This content has been marked as final. Show 16 replies
Hi there,Why do you believe the indexes need rebuilding? Just because they have a height of 4?
We have a number of indexes with a height of 3+ which
In general, you almost never have to rebuild a b-tree index in Oracle outside of data warehouses where it frequently makes sense to drop/ disable indexes prior to the nightly load process and to re-create/ enable them after the build.
Generally if a fairly large percentage of the table rows are deleted on a large table such that most of the index leaf blocks contain only one or two entries and the key values that would go into these blocks will not be repeated then rebuilding an index that meets these conditions will release a significant portion of the index space for reuse. This might be worthwhile if the index does not seem to reach a steady state (unchanging size) while the table size remains relatively unchanged.
For indexes that grow to a stready size relative to the table and remain at that size there is usually no reason to rebuild them.
HTH -- Mark D Powell --
Thanks to everyone that's replied.
And i do understand what's being said...
However, my manager wants to see the height of these indexes reduced in line with what he has read in 'expert' articles.
Please can someone advise how this would be possible?
Why is it not happening already with the commands i'm issuing?
user573914, notify your manager that the article he read contains flawed information as the height of an index is pretty much a direct result of the number of rows being indexed so rebuilding the index will normally not reduce the height of the index.
You can use the two previously provided links as support.
HTH -- Mark D Powell --
Many large indexes require a 4-level tree, it's normal!
However, building indexes in a large 32k blocksize can reduce the index levels, since the index nodes are tired to the blocksize:
I find that index rebuilding will help performance in specific cases, where large deletes have left fragmentation in the index blocks, BUT ONLY FOR indexes that have lots of multi-block reads (range scans, index FFS).
BTW, Oracle 10g has a segment advisor that will recommend indexes for rebuilding, but only from a space-saving perspective.
In sum, only indexes that experience milti-block reads (index range scans and Index FFS) would ever benefit from rebuilding, from a performance perspective.
HTH . . .
"root hog or die"
Yep, indexes are segments, and the segment advisor predicts space reclaimation from an index rebuild. Chris Foot, author of the "OCP Instructors Guide for Oracle DBA Certification", has these notes on the 10g segment advisor output:
Segment advisor, recommends for index rebuilding?
The output page also displays information that will help us determine if shrinking the segment is justified. The far right of each row displays:
- The amount of space allocated to the object.
- The amount of space that the object actually consumes.
- The amount of space that will be reclaimed if a shrink operation is performed.
- A recommendation stating if the shrink operation should be performed and if so, what steps should be taken to perform the shrink effectively.
Your indexes will never be used by the cbo if the clustering factor found in dba_indexes is almost equal to or greater than the amount of rows in the table. The cbo determines it is less expensive to do a full table scan than to revisit an index rowid. The solution to the problem is to rebuild the table which consolidates all the information for each row, reduces the clustering factor and allows the cbo to choose whether or not to still use the index.
hope this helps.
In my experience, that's not always true at all!
Your indexes will never be used by the cbo if the clustering factor found in dba_indexes is almost equal to or greater than the amount of rows in the table.
Yes, clustering factor is an issue, but most important is the estimated size of the result set from the query. If it's just a few rows, Oracle will use the index, regardless of clustering factor.