OraFAQ Forum: Server Administration » When does an index require to be rebuilt

Home » RDBMS Server » Server Administration » When does an index require to be rebuilt (Oracle Linux 6.7, Oracle 12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

When does an index require to be rebuilt [message #670930]

Sat, 04 August 2018 03:04

fabi88
Messages: 112
Registered: November 2011

Senior Member

Hi Expertsو

When does an index require to be rebuilt ? Is there any script to identify candidate indexes for rebuilding?

Report message to a moderator

Re: When does an index require to be rebuilt [message #670931 is a reply to message #670930]

Sat, 04 August 2018 05:14

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Simple answer: NEVER.

Report message to a moderator

Re: When does an index require to be rebuilt [message #670932 is a reply to message #670931]

Sat, 04 August 2018 06:53

fabi88
Messages: 112
Registered: November 2011

Senior Member

Thank for response.
Quote:

Simple answer: NEVER.

You mean that never rebuild indexes?

Report message to a moderator

Re: When does an index require to be rebuilt [message #670933 is a reply to message #670932]

Sat, 04 August 2018 07:06

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes.
Unless you delete most of the table rows and will never reinsert some.

[Updated on: Sat, 04 August 2018 07:09]

Report message to a moderator

Re: When does an index require to be rebuilt [message #670934 is a reply to message #670933]

Sat, 04 August 2018 07:10

fabi88
Messages: 112
Registered: November 2011

Senior Member

Thank you so much.

Report message to a moderator

Re: When does an index require to be rebuilt [message #671071 is a reply to message #670933]

Tue, 14 August 2018 04:40

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Michel Cadot wrote on Sat, 04 August 2018 13:06

Yes.
Unless you delete most of the table rows and will never reinsert some.

If you rely on indexes for fast min/max it can hurt you, if you delete rows and reinsert higher/lower values but it's uncommon.

Report message to a moderator

Re: When does an index require to be rebuilt [message #671104 is a reply to message #671071]

Wed, 15 August 2018 10:35

somasekar_n
Messages: 1
Registered: July 2005
Location: chennai

Junior Member

where there are more DML changes and Queries on the table shows poor response time check on the indexes status

Report message to a moderator

Re: When does an index require to be rebuilt [message #671105 is a reply to message #671104]

Wed, 15 August 2018 10:55

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What does this mean?
What do you mean by "indexes status"?
What do you mean by "check"?
Explain in details.

Report message to a moderator

Re: When does an index require to be rebuilt [message #671133 is a reply to message #670930]

Thu, 16 August 2018 06:34

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

fabi88 wrote on Sat, 04 August 2018 03:04

Hi Expertsو

When does an index require to be rebuilt ? Is there any script to identify candidate indexes for rebuilding?

Start here.
Richard Foote is widely considered to be the expert on Oracle indexes.

Report message to a moderator

Re: When does an index require to be rebuilt [message #671140 is a reply to message #671133]

Thu, 16 August 2018 07:29

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Good link. Thanks, Ed.

Report message to a moderator

Re: When does an index require to be rebuilt [message #671147 is a reply to message #671140]

Thu, 16 August 2018 08:28

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I once wrote a 33-page document (in 2006), quite similar to Richard Foote's presentation, unfortunately in French whose summary was:

No systematic reorganization is beneficial
Some reorganizations may be beneficial in specific cases
Many of these cases can be avoided with good storage settings or a good application
In the case where we have to reorganize anyway, a carefully attention must be taken to the PCTFREE storage parameter to avoid degrading the performances of the work that will follow.

Report message to a moderator

Re: When does an index require to be rebuilt [message #671374 is a reply to message #671133]

Sun, 26 August 2018 23:29

fabi88
Messages: 112
Registered: November 2011

Senior Member

Quote:

Start here.
Richard Foote is widely considered to be the expert on Oracle indexes.

Excellent link, Thank you so much.

Report message to a moderator

Previous Topic:	.patch_storage cleanup
Next Topic:	Instance duplication, Bi-directional implementation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 21:51:55 CST 2025