Home » RDBMS Server » Server Administration » When does an index require to be rebuilt (Oracle Linux 6.7, Oracle 12.1.0.2.0)
When does an index require to be rebuilt [message #670930] Sat, 04 August 2018 03:04 Go to next message
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?

Re: When does an index require to be rebuilt [message #670931 is a reply to message #670930] Sat, 04 August 2018 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Simple answer: NEVER.

Re: When does an index require to be rebuilt [message #670932 is a reply to message #670931] Sat, 04 August 2018 06:53 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thank for response.
Quote:

Simple answer: NEVER.
You mean that never rebuild indexes?
Re: When does an index require to be rebuilt [message #670933 is a reply to message #670932] Sat, 04 August 2018 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
fabi88
Messages: 112
Registered: November 2011
Senior Member
Thank you so much.
Re: When does an index require to be rebuilt [message #671071 is a reply to message #670933] Tue, 14 August 2018 04:40 Go to previous messageGo to next message
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.
Re: When does an index require to be rebuilt [message #671104 is a reply to message #671071] Wed, 15 August 2018 10:35 Go to previous messageGo to next message
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
Re: When does an index require to be rebuilt [message #671105 is a reply to message #671104] Wed, 15 August 2018 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

Re: When does an index require to be rebuilt [message #671133 is a reply to message #670930] Thu, 16 August 2018 06:34 Go to previous messageGo to next message
EdStevens
Messages: 1293
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.
Re: When does an index require to be rebuilt [message #671140 is a reply to message #671133] Thu, 16 August 2018 07:29 Go to previous messageGo to next message
gazzag
Messages: 1091
Registered: November 2010
Location: Bedwas, UK
Senior Member
Good link. Thanks, Ed.
Re: When does an index require to be rebuilt [message #671147 is a reply to message #671140] Thu, 16 August 2018 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: When does an index require to be rebuilt [message #671374 is a reply to message #671133] Sun, 26 August 2018 23:29 Go to previous message
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.
Previous Topic: .patch_storage cleanup
Next Topic: Instance duplication, Bi-directional implementation
Goto Forum:
  


Current Time: Wed Jan 27 01:11:35 CST 2021