Home » RDBMS Server » Server Administration » Index rebuild ...Any risk in this ?
Index rebuild ...Any risk in this ? [message #329663] Thu, 26 June 2008 03:02 Go to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
Guys,

Is there any risk when we rebuild an Index ? adn what exactly happens when we do a rebuild?

Arvind

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329699 is a reply to message #329663] Thu, 26 June 2008 05:06 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
During index rebuilding the table on which the index rebuilding going on will be locked for DML operations till rebuilding gets completed.
You can have index rebuilding with ONLINE to avoid locking.

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329705 is a reply to message #329699] Thu, 26 June 2008 05:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> You can have index rebuilding with ONLINE to avoid locking.
And have tablespace doubled in size after an online rebuild.
Question is, why do you need to rebuild?

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329711 is a reply to message #329663] Thu, 26 June 2008 05:26 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Quote:

And have tablespace doubled in size after an online rebuild.


It is right that tablespace will take the same amount of size as the size of the index. It is used for the temporary segment and once rebuilding completed the space will be released.

Question is, why do you need to rebuild?

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329714 is a reply to message #329711] Thu, 26 June 2008 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
It is used for the temporary segment and once rebuilding completed the space will be released.

Released for other segments not released in OS.

In addition, using "online" may lead to ORA-01555 or ORA-01410 errors.

Regards
Michel

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329737 is a reply to message #329663] Thu, 26 June 2008 06:50 Go to previous messageGo to next message
VenkatMethuku
Messages: 8
Registered: June 2008
Junior Member
what is the impact?

Regards,
Venkat.

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329738 is a reply to message #329737] Thu, 26 June 2008 07:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> what is the impact?
A big bang? May be or may not be.
Please read all the above responses.

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329741 is a reply to message #329737] Thu, 26 June 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
VenkatMethuku wrote on Thu, 26 June 2008 13:50
what is the impact?

Regards,
Venkat.

Don't you read the posts?

Regards
Michel

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329754 is a reply to message #329663] Thu, 26 June 2008 07:35 Go to previous messageGo to next message
VenkatMethuku
Messages: 8
Registered: June 2008
Junior Member
i had already read, just i asked the impact for this ORA errors.
ORA-01555 or ORA-01410 errors.

Regards,
Venkat.

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #329758 is a reply to message #329754] Thu, 26 June 2008 07:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Abort of the current query or procedure not the one that rebuilds, the other ones.

Regards
Michel

[Updated on: Thu, 26 June 2008 07:39]

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #330042 is a reply to message #329663] Fri, 27 June 2008 07:35 Go to previous messageGo to next message
ArvindBhope
Messages: 55
Registered: June 2008
Member
This may help us to an extent......


[mod-edit] removed copyrighted Metalink article. It can be found here:

https://metalink.oracle.com/metalink/plsql/f?p=130:14:6301034043256205447::::p14_database_id,p14_docid,p14_show_header,p14_show_help, p14_black_frame,p14_font:NOT,182699.1,1,1,1,helvetica

[Edit MC: duplicated post removed]

[Updated on: Fri, 27 June 2008 08:21] by Moderator

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #330055 is a reply to message #329663] Fri, 27 June 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I replaced cut and paste of Metalink articles with links.

Report message to a moderator

Re: Index rebuild ...Any risk in this ? [message #330201 is a reply to message #329663] Fri, 27 June 2008 22:25 Go to previous message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
Hi,

I may add a risk: leaf block splitting.

When you rebuild the index it will use the PCTFREE value used at creation time, and according to it will fill blocks until only PCTFREE space is available; as PCTFREE gets near 0, the risk (and overhead) of leaf block splitting increases.

I recommend this excellent document for details
Richard Foote's: Index Internals-Rebuilding the truth

And this series for particular info regarding index key compression
Honey, I shrunk the indexes-Part 1
Honey, I shrunk the indexes-Part 2
Honey, I shrunk the indexes-Part 3

Regards

Report message to a moderator

Previous Topic: why oracle doestn't start database after closing
Next Topic: Corrupt objects.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 22:34:50 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.