Forum Stats

  • 3,817,240 Users
  • 2,259,294 Discussions
  • 7,893,710 Comments

Discussions

index rebuilding process

668822
668822 Member Posts: 901
edited Jan 22, 2009 6:17AM in General Database Discussions
hi masters,

it is "NOT" yet another thread on index rebuilding.

i read every thread on index rebuilding on oracle forums. every thread is full of load with arguments and dramatically valuable discussion.

but hardly some thread discuss the process of index rebuilding!!!!!

i would like to know what exactly oracle do, to rebuild the index??? does it use existing index as a source and hence require more space??

or it generates more redo therefore requires more space??

what oracle do with old index??? does it releases space occupied by old index???

if anyone have any link or suggestion then i will be thankful. and more deep knowledge sharing is appreciated.


thanks and regards
VD

Edited by: vikrant dixit on Jan 21, 2009 3:40 AM
Tagged:

Best Answer

  • Charles Hooper
    Charles Hooper Member Posts: 1,317 Gold Badge
    Answer ✓
    vikrant dixit wrote:
    thanks Charles


    for your kind support. and still i doubt how much performance gain should be xpected by index rebuilding. i think i have to read all that thread once again, and find the cream of the topic.


    and whatever said by experts isn't it true that performance gain is depend on scenario??


    anyways thanks

    thanks and regards
    VD
    Vikrant,

    Based on what I have seen, and read, what you stated above is correct.

    I like your thread topic of attempting to understand what happens during an index rebuild, and after an index rebuild. The topic of whether or not to rebuild indexes has been discussed many, many times on this forum, in Usenet posts dating back to at least 1999, in various threads on asktom.oracle.com, in various websites, and in various books. But, your topic of what happens during an index rebuild, and after an index rebuild is not discussed quite as often, so hopefully the links that I provided to you will help.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
«1

Answers

  • hafeezkalsekar
    hafeezkalsekar Member Posts: 11 Blue Ribbon
    HI

    WHEN WE REBUILD THE INDEX , ORACLE FIRST CREATE A NEW INDEX AND THEN DROPS THE OLD INDEX .
    SINCE NEW INDEX IS CREATED FIRST , WE NEED SAME AMOUNT OF SPACE AS OF INDEX IN TABLESPACE .

    HENCE REDO IS ALSO CREATED
  • Charles Hooper
    Charles Hooper Member Posts: 1,317 Gold Badge
    Vikrant,

    Richard Foote has a series of well written articles describing what happens during and after an index rebuild, you might start with the last article at the following link and work your way up to the first article - the Power Point presentation linked at the bottom of the page will be very helpful to you:
    http://richardfoote.wordpress.com/category/index-shrink/

    Another of Richard's articles which might be helpful:
    http://richardfoote.wordpress.com/2008/02/11/index-create-and-rebuild-locking-improvements-in-11g-ch-ch-ch-changes/

    Discussion of the potential benefits and side effects of index rebuilds (the potential for additional redo generation after the rebuild completes is one of the side effects):
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35336203098853

    Other links which may be helpful to answer your questions:
    http://jonathanlewis.wordpress.com/2008/02/09/index-rebuild-10g/
    http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 3530
    3530 Member Posts: 459
    To check weather **space is released after index is rebuilt* or not* is simple enough thing you can test on your own.
  • 668822
    668822 Member Posts: 901
    hi charles sir,


    i ahve this document of richard foote


    http://bbs.wisky.org/doc/internal.docs/Richard%20Foote-Oracle%20Index%20Internal.pdf

    and this by Jonathan Lewis

    http://www.dbazine.com/oracle/or-articles/jlewis14


    but in both docs when, why and how to rebuild index is discussed. i will read docs provided by you.


    thanks for your support. and how to check space released by index then?


    thanks and regards
    VD
  • Charles Hooper
    Charles Hooper Member Posts: 1,317 Gold Badge
    Vikrant,

    I understand that there were a couple revisions of Richard Foote's Power Point presentation. It is probably best to obtain the presentation from the source:
    http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf

    The first link that I provided to you in my last post showed how to use the ANALYZE INDEX command to determine the amount of space used by the index. Due to the table locking required by ANALYZE INDEX, I suggest that you try the command in a test database, or a test table in a database - Richard provides scripts which may be helpful for test purposes.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 668822
    668822 Member Posts: 901
    thanks Charles


    for your kind support. and still i doubt how much performance gain should be xpected by index rebuilding. i think i have to read all that thread once again, and find the cream of the topic.


    and whatever said by experts isn't it true that performance gain is depend on scenario??


    anyways thanks

    thanks and regards
    VD
  • Charles Hooper
    Charles Hooper Member Posts: 1,317 Gold Badge
    Answer ✓
    vikrant dixit wrote:
    thanks Charles


    for your kind support. and still i doubt how much performance gain should be xpected by index rebuilding. i think i have to read all that thread once again, and find the cream of the topic.


    and whatever said by experts isn't it true that performance gain is depend on scenario??


    anyways thanks

    thanks and regards
    VD
    Vikrant,

    Based on what I have seen, and read, what you stated above is correct.

    I like your thread topic of attempting to understand what happens during an index rebuild, and after an index rebuild. The topic of whether or not to rebuild indexes has been discussed many, many times on this forum, in Usenet posts dating back to at least 1999, in various threads on asktom.oracle.com, in various websites, and in various books. But, your topic of what happens during an index rebuild, and after an index rebuild is not discussed quite as often, so hopefully the links that I provided to you will help.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 668822
    668822 Member Posts: 901
    ya Charles sir,


    definitely links provided by you were really of great help. hope after reading all the links i will get what i exactly want.


    thank and regards
    VD
  • 681022
    681022 Member Posts: 4
    Hello Vikrant
    "i would like to know what exactly oracle do, to rebuild the index??? "
    Oracle reads the existing index in sorted order quickly creating a new index in temp degment and renaming the temp segments to the index after sucessful completion.
    "does it releases space occupied by old index???"
    Yes, that why the Oracle segment advisor in OEM tells you when to rebuild indices.

    If you assume that the new state of an index isoptimal, then a rebuild might improve performance.
    "if anyone have any link or suggestion then i will be thankful."
    http://www.google.si/search?hl=en&&q=oracle+index+rebuild
  • 668822
    668822 Member Posts: 901
    edited Jan 22, 2009 1:00AM
    hi Oldeolthof

    thanks for your post


    thanks and regards
    VD

    Edited by: vikrant dixit on Jan 21, 2009 9:56 PM

    Edited by: vikrant dixit on Jan 21, 2009 9:57 PM

    Edited by: vikrant dixit on Jan 21, 2009 9:58 PM

    Edited by: vikrant dixit on Jan 21, 2009 9:58 PM

    Edited by: vikrant dixit on Jan 21, 2009 9:59 PM

    Edited by: vikrant dixit on Jan 21, 2009 9:59 PM
This discussion has been closed.