CREATE INDEX vs. ALTER INDEX REBUILD
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: CREATE INDEX vs. ALTER INDEX REBUILD

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    CREATE INDEX vs. ALTER INDEX REBUILD

    Can anybody point me to some documentation about what goes on during a CREATE INDEX and a ALTER INDEX REBUILD? I am trying to figure out if ALTER INDEX REBUILD reads the old index to create the "rebuilt" index, or if the REBUILD goes back to the table to create the "rebuilt" index. I have an idea, but I need definitive proof.

    For example, if I have a table (XYZ) that has 1 million rows and an index (XYZ_PK) that is a unique index on that table. If I issue CREATE UNIQUE INDEX xyz_pk ON xyz(x) of course every row in XYZ will be read. However, if I issue ALTER INDEX xyz_pk REBUILD, does Oracle read XYZ_PK and re-organize the blocks or does it read XYZ to build the new index?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Use the ALTER INDEX ... REBUILD statement to reorganize or compact an existing index or to change its storage characteristics. The REBUILD statement uses the existing index as the basis for the new one. All index storage statements are supported, such as STORAGE (for extent allocation), TABLESPACE (to move the index to a new tablespace), and INITRANS (to change the initial number of entries).

    ALTER INDEX ... REBUILD is usually faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O then discards the branch blocks. A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress.

    http://download-west.oracle.com/docs..._acce.htm#7531


    Does that help?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    That would be it! I've been looking at the Concepts and Admin guide for over an hour...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Feb 2003
    Location
    Kolkata, India
    Posts
    33
    When rebuilding a normal index, the statistics for that index are not affected.

    SQL> create index x_test on test ( y ) global;

    Index created.

    SQL> analyze table test compute statistics;

    Table analyzed.

    SQL> select index_name, leaf_blocks from user_indexes;

    INDEX_NAME LEAF_BLOCKS
    ------------------------------ -----------
    X_TEST 3

    SQL> alter index X_TEST rebuild;

    Index altered.

    SQL> select index_name, leaf_blocks from user_indexes;

    INDEX_NAME LEAF_BLOCKS
    ------------------------------ -----------
    X_TEST 3

    Thus no change to the statistics when the index has been rebuilt.
    Last edited by arjun; 02-20-2003 at 01:39 PM.
    ("`-''-/").___..--''"`-._
    `6_ 6 ) `-. ( ).`-.__.`)
    (_Y_.)' ._ ) `._ `. ``-..-'
    _..`--'_..-_/ /--'_.' ,'
    (((' (((-((('' ((((

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Well, in that case.......ahem...

    IT TOOK ME 30 SECONDS TO FIND THIS ON TAHITI, I SUGGEST YOU DO A LITTLE HOMEWORK BEFORE POSTING HERE!!


    C'mon, I've been waiting to say that for at least 2 years.

    Who do you love?

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Thumbs up

    Originally posted by Mr.Hanky
    C'mon, I've been waiting to say that for at least 2 years.
    U got patience man...
    2 years of waiting to be able to say those words...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by adewri
    U got patience man...
    2 years of waiting to be able to say those words...
    U do as well to reply......

    n ofcourse myself to point u.......
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Originally posted by arjun
    When rebuilding a normal index, the statistics for that index are not affected.

    SQL> create index x_test on test ( y ) global;

    Index created.

    SQL> analyze table test compute statistics;

    Table analyzed.

    SQL> select index_name, leaf_blocks from user_indexes;

    INDEX_NAME LEAF_BLOCKS
    ------------------------------ -----------
    X_TEST 3

    SQL> alter index X_TEST rebuild;

    Index altered.

    SQL> select index_name, leaf_blocks from user_indexes;

    INDEX_NAME LEAF_BLOCKS
    ------------------------------ -----------
    X_TEST 3

    Thus no change to the statistics when the index has been rebuilt.
    When you rebuild them then you must know why you're rebuilding them.
    You want them to stay "compact" as you planned or make them airy so the levels don't increase that fast...and then add pctfree in that clause............
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width