index rebuilding process

668822 · Jan 21, 2009 6:40AM

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

Charles Hooper · Jan 21, 2009 7:46AM

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.

hafeezkalsekar · Jan 21, 2009 6:56AM

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 · Jan 21, 2009 7:03AM

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 · Jan 21, 2009 7:04AM

To check weather **space is released after index is rebuilt* or not* is simple enough thing you can test on your own.

668822 · Jan 21, 2009 7:13AM

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 · Jan 21, 2009 7:26AM

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 · Jan 21, 2009 7:35AM

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 · Jan 21, 2009 7:46AM

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 · Jan 21, 2009 7:52AM

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 · Jan 21, 2009 9:42AM

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 · Jan 22, 2009 12:45AM

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

Forum Stats

Quick Links

Categories

index rebuilding process

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

index rebuilding process

Best Answer

Answers