This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jun 26, 2007 4:48 AM by 258794 RSS

Rebuilding indexes - height not reduced?

576917 Newbie
Currently Being Moderated
Hi there,
We have a number of indexes with a height of 3+ which need rebuilding.

Against these indexes i issued: alter index (index_name) rebuild;

When i checked them afterwards they were all exactly the same height...

I ran through it again with one index:
==========================

SQL> analyze index eclipse.TRAD_HIST_IDX6 validate structure;

SQL> select name,height from index_stats;


NAME HEIGHT
------------------------------ ---------
TRAD_HIST_IDX6 4


SQL> alter index TRAD_HIST_IDX6 rebuild;


SQL> analyze index eclipse.TRAD_HIST_IDX6 validate structure;

SQL> select name,height from index_stats;


NAME HEIGHT
------------------------------ ----------
TRAD_HIST_IDX6 4


====================

Ideas anyone??
Rup
  • 1. Re: Rebuilding indexes - height not reduced?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Hi there,
    We have a number of indexes with a height of 3+ which
    need rebuilding.
    Why do you believe the indexes need rebuilding? Just because they have a height of 4?

    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.

    Justin
  • 2. Re: Rebuilding indexes - height not reduced?
    LEARNING_ORACLE Newbie
    Currently Being Moderated
    Hi Justin,

    Then How to decide when to rebuild the index.

    Thanks in advance
  • 3. Re: Rebuilding indexes - height not reduced?
    Justin Cave Oracle ACE
    Currently Being Moderated
    Take a look through Richard Foote's canonical presentation on the topic

    http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf

    The short answer is "almost never".

    Justin
  • 4. Re: Rebuilding indexes - height not reduced?
    181444 Expert
    Currently Being Moderated
    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 --
  • 5. Re: Rebuilding indexes - height not reduced?
    576917 Newbie
    Currently Being Moderated
    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?

    Thanks
    Rup
  • 6. Re: Rebuilding indexes - height not reduced?
    6363 Guru
    Currently Being Moderated
    Did you read the PDF posted?

    Here is another link

    http://www.jlcomp.demon.co.uk/communication.html
    Why is it not happening already with the commands i'm issuing?
    I did, however, point out that rebuilding an index was in most cases unlikely to change the index height.
  • 7. Re: Rebuilding indexes - height not reduced?
    181444 Expert
    Currently Being Moderated
    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 --
  • 8. Re: Rebuilding indexes - height not reduced?
    APC Oracle ACE
    Currently Being Moderated
    user573914, notify your manager that the article he read contains flawed information
    Alternatively, send them to this forum and we'll give 'em a slap! Shock-a-boo-boo!

    Cheers, APC
  • 9. Re: Rebuilding indexes - height not reduced?
    108476 Journeyer
    Currently Being Moderated
    Hi Rupert,

    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:

    http://www.dba-oracle.com/art_so_blocksize.htm

    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).

    http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm

    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 . . .

    Don Burleson
    "root hog or die"
  • 10. Re: Rebuilding indexes - height not reduced?
    Luckys Pro
    Currently Being Moderated
    Segment advisor, recommends for index rebuilding?
  • 11. Re: Rebuilding indexes - height not reduced?
    108476 Journeyer
    Currently Being Moderated
    Hi,
    Segment advisor, recommends for index rebuilding?
    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:

    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.
  • 12. Re: Rebuilding indexes - height not reduced?
    258794 Newbie
    Currently Being Moderated
    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.

    al
  • 13. Re: Rebuilding indexes - height not reduced?
    108476 Journeyer
    Currently Being Moderated
    Hi Al,
    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.
    In my experience, that's not always true at all!

    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.
  • 14. Re: Rebuilding indexes - height not reduced?
    Ramesh Eega Explorer
    Currently Being Moderated
    wolfgang's ioug-paper explains that low value of table selecitivity is still potantial candidate.

    cost =      blevel +
         ceil(selectivity * leaf_blocks) +
         ceil( selectivity * clustering_factor)
1 2 Previous Next