Forum Stats

  • 3,837,863 Users
  • 2,262,303 Discussions
  • 7,900,414 Comments

Discussions

Index Rebuild ?

587671
587671 Member Posts: 768
edited Nov 20, 2007 2:08AM in General Database Discussions
How can I tell if my Db indexes require rebuilding.. when should I rebuild.. ?
«13

Comments

  • 153119
    153119 Member Posts: 5,173
    Generally speaking you should not rebuild.
    There has been a recent thread discussing this ad nauseam.
    Please always search the forum before asking questions like this again!

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 591663
    591663 Member Posts: 156
    Make use of statspack or AWR.

    Also check the status of te indexes of your database using following query.

    SQL> SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS FROM DBA_INDEXES WHERE STATUS='INVALID';
  • 108476
    108476 Member Posts: 2,184
    edited Nov 19, 2007 5:50AM
    Hi,
    How can I tell if my Db indexes require rebuilding
    First, see the Oracle segment advisor which recommends indexes for rebuilding:

    http://www.rampant-books.com/art_floss_segments_shrink.htm

    ---------------------------------------

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

    ---------------------------------------

    Metalink Note:46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE" says that there are some tangible benefits to using a larger blocksize:

    - Using bigger blocks means more data transfer per I/O call; this is an advantage since the cost of I/O setup dominates the cost of an I/O. . .

    - Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

    - Using a block size that is k times bigger than your current one will save you (k-1)f/(kb-f) bytes of space for large segments, where f is the size of a block's fixed block header (61 bytes for tables, 57+4n for n-table clusters, 113 for indexes). For example, you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size.

    - When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.

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


    Hope this helps. . .

    Don Burleson
    Oracle Press author
  • antti.koskinen
    antti.koskinen Member Posts: 402
    edited Nov 19, 2007 6:17AM
    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.
    And as for any other rule of thumb, there are exceptions. A large enough index might not fit into 5 levels, rebuilding an index after a mass delete might not be sensible if there's another big delete later in the batch, if the index is only range scanned etc..

    edit: removed "quote" tags

    Message was edited by:
    antti.koskinen

    Message was edited by:
    antti.koskinen
  • 108476
    108476 Member Posts: 2,184
    Hi Antti,
    And as for any other rule of thumb, there are exceptions.
    Yes, good observation.

    In my experience, it also depends on how the index is used. Indexes that are used for a row ID fetch will never see a performanceimprovement from an index rebuild.
  • 94799
    94799 Member Posts: 2,208
    Indexes that are used for a row ID fetch
    Do you mean single row fetch? The term 'row ID fetch' seems rather ambiguous to me.
  • 601585
    601585 Member Posts: 645
    edited Nov 19, 2007 7:59AM
    Do you really know what you mean?
    For instance ...
    When an index is skewed
    What on earth do you mean by "skewed"?
    Does this mean that there are many free leaf blocks, or leaf nodes are splitted quite often by right-handed insertion?
    "skewed" is such an inappropriate term for index.
    Quite confusing...
    parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance
    What on earth is this related to index rebuild?
    "Parts of index are accessed more frequently than others"? Does this mean your index is right-handed? If then, what on earth does right-handed index have to do with "index rebuild"?

    Please don't just spread what you read in the metalink.
    Didn't you ever think that the note you refered has a chance to contain some flaws?
    You're a decent consultant(or engineer?), then you must verify or at least
    have question before you distribute.

    PS) In addition, what on earth does index block size have to do with "index rebuild"?
    Your whole excerpts and suggestions are confusing to me, and probably to others.

    Typo...
    Message was edited by:
    Dion_Cho
  • 108476
    108476 Member Posts: 2,184
    Hi James,
    single row fetch?
    Yes, that's much better. . . . Thanks!
  • 108476
    108476 Member Posts: 2,184
    edited Nov 19, 2007 8:20AM
    Hi Dion
    Please don't just spread what you read in the metalink.
    MetaLink and the docs are the "official" word from Oracle, right? When I quote my notes, people chastise me for NOT citing the docs! I can't win!
    What on earth do you mean by "skewed"?
    That wasn't me, I just quoted the docs . . . .

    To me, the "skew" is misunderstood, as is "unbalanced", and the question about whether Oracle indexes are self-balancing is largely a matter of semantics. As rows are added to an empty index, Oracle controls the addition of same-level blocks (called “splitting”) until the higher-level index node is unable to hold any more key-pointer pairs. When the index can no longer split (because the owner block is full), Oracle will spawn a whole new index level, keeping the index tree in perfect logical and physical balance.

    However, deletes are a different story. Physically, Oracle indexes are always balanced because empty blocks stay inside the tree structure after a massive delete. Logically, Oracle indexes are not self-balancing because Oracle does not remove the dead blocks as they become empty.

    This type of “sparse” index is typical of an index on highly-active tables with large-scale inserts, deletes and updates. We may have thousands of empty or near-empty index blocks, and several Oracle execution plans will run longer on this type of “sparse” index

    Didn't you ever think that the note you refered has a chance to contain some flaws?
    Oh sure. I have my own approach, but I wanted to be "politically correct", and cite the official docs. Here are my personal notes:

    http://www.dba-oracle.com/t_index_rebuilding_issues.htm
    what on earth does index block size have to do with "index rebuild"?
    Excellent question. The benefits of large blocksizes are demonstrated on this OTN thread where we see a demo showing 3x faster performance using a larger block size:

    568662

    Also, Robin Schumacher has proved that indexes build differently in different blocksizes:

    http://www.rampant-books.com/book_2003_1_perf.htm

    “As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache.

    Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database.“

    Hope this helps. . .

    Don Burleson
    Oracle Press author
  • 601585
    601585 Member Posts: 645
    edited Nov 19, 2007 8:19AM
    Don, please anwer my question~
    I suddenly realized that you're totally ignoring my questions.
    That's because my English is not natural and fluent?
    I'm trying really hard to write good English. :)

    Please~~~
    You're such a famous person in my region just like other famous engineers including Jonathan, Tom, Tanel and Julian... (but not exactly the same way)

    So your technical anwser will be honor~

    Oops. you answered my question. Sorry. :)
    Message was edited by:
    Dion_Cho
This discussion has been closed.