Forum Stats

  • 3,770,167 Users
  • 2,253,079 Discussions
  • 7,875,357 Comments

Discussions

Index Rebuilding

1011859
1011859 Member Posts: 59
edited Jul 23, 2013 5:36AM in General Database Discussions

Hi All,

How to perform index rebuilding on the oracle database standard edition?

Thanks and Regards

Akhil

Tagged:
DB_Janitor

Answers

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    ALTER INDEX index_name REBUILD
    

    Altering Indexes

    Of course, it is highly unusual to need to rebuild an index in Oracle.  If you don't know how and aren't familiar enough with the Oracle documentation to look it up yourself, I would strongly suspect that you don't need to rebuild an index.  Furthermore, I'd wager that you'll create far more performance issues than you'll solve if you succeed in rebuilding the index.

    Justin

    DB_Janitor
  • 1011859
    1011859 Member Posts: 59

    Thanks for quick reply.. i have already read this index rebuild document.

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown

    OK.  Then what is your question?  Why do you believe that you need to rebuild an index in the first place?

    Justin

  • Mr.D.
    Mr.D. Member Posts: 644

    Index defragmented? High Clustering Factor?

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown

    Sorry, I'm not sure that I understand.  Are you suggesting that those are reasons to rebuild an index?

    Justin

  • Mr.D.
    Mr.D. Member Posts: 644

    Maybe,

    if your table is subject of many delete.

  • 1011859
    1011859 Member Posts: 59
    edited Jul 23, 2013 5:09AM

    In the perspective of oracle standard edition if i have large database and decrease of performance due to large index fragmentation or if  unable to retrieve data from a search.

    And also about online indexing is the feature of EE so when we run alter index index_name rebuild; in SE.. so what kind of mode its going to perform

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown

    First off, I would strongly suggest a thorough reading of the seminal presentation on the topic, Richard Foote's "Rebuilding the Truth"

    Index Internals – Rebuilding The Truth | Richard Foote's Oracle Blog

    If you have a monotonically increasing column and you are regularly deleting older data but leaving just enough data on the left-hand side of the index that Oracle can't free up the index blocks itself, it may may sense to coalesce the index, not to rebuild it.

    As for the clustering factor, I don't see how that is relevant.  The clustering factor measures how ordered the table is with respect to the index.  Rebuilding the index doesn't change that.  Rebuilding the table would.  But, first off, if you really care about the physical order of rows in a table, you really ought to be using an appropriate data structure-- either an index-organized table or a cluster.  And if you rebuild the table so that it is better physically organized for one index, you'll very likely make it less organized according to all the other indexes. 

    Justin

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown

    What makes you believe that you have a large amount of "index fragmentation"?  What, specifically, does that mean to you?

    What makes you believe that you are getting incorrect results from your queries?  What makes you believe that rebuilding an index would cause the results of a query to change?

    Justin

  • A standard index consists of a B+ -tree. The 'B' stands for 'balanced'. This means only if you monotically add keys to one end of the tree, you may get an extra level.

    Deleted leaf nodes are reused by Oracle.

    You need to explain what 'fragmentation' is to you, as the index will always be balanced.

    -----------

    Sybrand Bakker

    Senior Oracle DBA

This discussion has been closed.