Home » RDBMS Server » Server Administration » Reindex database
Reindex database [message #256655] Mon, 06 August 2007 04:39 Go to next message
win3vin
Messages: 35
Registered: April 2007
Location: Malaysia
Member
Hi Guru,

What is the best pratice to reindex the database?

I'm using below syntax to reindex the indexes only. I try 'reindex database <instance>', but it wouldn't work.

ALTER INDEDX <index name> REBUILD [[ONLINE]];

Please help. Thanks.
Re: Reindex database [message #256662 is a reply to message #256655] Mon, 06 August 2007 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to do it index by index.
It's easy to generate such a script as you have all index name in dba_indexes.
However, it's a very bad idea to do so.

Regards
Michel
Re: Reindex database [message #256960 is a reply to message #256662] Tue, 07 August 2007 05:06 Go to previous messageGo to next message
groesbeek
Messages: 9
Registered: August 2007
Location: Netherlands
Junior Member
>>However, it's a very bad idea to do so.

Why? It can be good for the performance to rebuild indexes for some users ..

Regards,
Groesbeek
Re: Reindex database [message #256969 is a reply to message #256960] Tue, 07 August 2007 05:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Are you saying that it is a good idea to rebuild ALL indexes in a database? Or am I misunderstanding you and you are saying that it is a good idea to rebuild SOME indexes to improve performance and you have misunderstood what Michel said?
Re: Reindex database [message #256973 is a reply to message #256960] Tue, 07 August 2007 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to pablolee's answer: in 95% of cases it is a bad idea to rebuild application indexes.

Regards
Michel
Re: Reindex database [message #256976 is a reply to message #256969] Tue, 07 August 2007 05:23 Go to previous messageGo to next message
groesbeek
Messages: 9
Registered: August 2007
Location: Netherlands
Junior Member
>>Why? It can be good for the performance to rebuild indexes >>for some users ..

some users..
Re: Reindex database [message #256981 is a reply to message #256976] Tue, 07 August 2007 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correction: very few users.

Regards
Michel
Re: Reindex database [message #256987 is a reply to message #256976] Tue, 07 August 2007 05:44 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
underlining the word some in the context of users has NOTHING to do with what I asked you, nor does it correctly relate to the actual question that has been asked by the OP. It does however imply to me that you have misunderstood the question. The point that Michel was making (I'm putting on my mind reader's cap now- correct me if I'm wrong Michel), is that it would be a very bad idea to rebuild ALL indexes in the database. And I would add that this would be bad for all users in terms of performance.
Re: Reindex database [message #256995 is a reply to message #256987] Tue, 07 August 2007 06:33 Go to previous messageGo to next message
groesbeek
Messages: 9
Registered: August 2007
Location: Netherlands
Junior Member
All users which containt constantly changing data should have their indexes rebuild frequently. Depending on your database that will be very few or some of the users.

This is what I thought. Please give me your opinion.

Regards,
Groesbeek
Re: Reindex database [message #257007 is a reply to message #256995] Tue, 07 August 2007 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
All users which containt constantly changing data should have their indexes rebuild frequently

This is wrong for 99% of cases.

Regards
Michel

[Edit: change 98 to 99]

[Updated on: Tue, 07 August 2007 06:56]

Report message to a moderator

Re: Reindex database [message #257014 is a reply to message #257007] Tue, 07 August 2007 07:08 Go to previous messageGo to next message
groesbeek
Messages: 9
Registered: August 2007
Location: Netherlands
Junior Member
>>This is wrong for 99% of cases.
OK, but I was pretty sure about my statement. Can you please explain to me why it's wrong in 99% of casses?

Thanks

Regards,
Groesbeek
Re: Reindex database [message #257016 is a reply to message #256995] Tue, 07 August 2007 07:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Users do not contain data. SCHEMAs contain SEGMENTS which contain data. And as Michel said you will find that you are wrong in your assertion
Re: Reindex database [message #257030 is a reply to message #257014] Tue, 07 August 2007 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post why you think it is good and I'll explain you why it is wrong.

Regards
Michel
Re: Reindex database [message #257033 is a reply to message #256655] Tue, 07 August 2007 07:38 Go to previous messageGo to next message
Amersfoort
Messages: 3
Registered: August 2007
Junior Member
Schema's contain tables. Tables contain data in different segments. Indexes are created on the tables.

Schema's with a lot of data will have a lot of indexes. The indexes need to be rebuilded frequently for optimal performance.
Re: Reindex database [message #257037 is a reply to message #257033] Tue, 07 August 2007 07:42 Go to previous messageGo to next message
groesbeek
Messages: 9
Registered: August 2007
Location: Netherlands
Junior Member
>>The indexes need to be rebuilded frequently for optimal performance.

Offcourse it is not wise to "just" rebuild the whole database without perform an analyse of the tables, but I personaly don't think it would harm to analyse users (or schemas) for a better performance.
Re: Reindex database [message #257043 is a reply to message #257037] Tue, 07 August 2007 08:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

Oracle Corporation also recommended for anaylze schema statistics in regular interval for bettere excution plan for CBO.

in 10g Oracle Server automatically gather database statistics.


Regards
Taj
Re: Reindex database [message #257045 is a reply to message #257033] Tue, 07 August 2007 08:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Schema's contain tables
correct
Quote:
Tables contain data in different segments
Incorrect. Tables are segments. Any object that takes up space on disk is called a segmnent. Indexes are also segments.
Quote:
Schema's with a lot of data will have a lot of indexes.

Bit of a sweeping generalisation there, but OK, I'll play along.
Quote:
The indexes need to be rebuilded frequently for optimal performance.
Wrong.
Re: Reindex database [message #257047 is a reply to message #257033] Tue, 07 August 2007 08:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Amersfoort wrote on Tue, 07 August 2007 08:38
Schema's contain tables. Tables contain data in different segments. Indexes are created on the tables.

Schema's with a lot of data will have a lot of indexes. The indexes need to be rebuilded frequently for optimal performance.


Wow, I can't believe I just read that. Are you getting your advice from Don Burleson? You might want to look at Jonethan Lewis' article on indexes http://www.jlcomp.demon.co.uk/indexes_i.html
Re: Reindex database [message #257048 is a reply to message #257047] Tue, 07 August 2007 08:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Are you getting your advice from Don Burleson?

Zing! Ouch Very Happy
Re: Reindex database [message #257050 is a reply to message #257037] Tue, 07 August 2007 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but I personaly don't think it would harm to analyse users (or schemas) for a better performance.

Don't change the subject.
We are talking about rebuilding the indexes not analyzing.

Regards
Michel
Re: Reindex database [message #257051 is a reply to message #257033] Tue, 07 August 2007 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The indexes need to be rebuilded frequently for optimal performance.

Ooops! Too late to answer to that, joy_division already did it and well.

Regards
Michel
Re: Reindex database [message #257137 is a reply to message #257047] Tue, 07 August 2007 11:28 Go to previous message
groesbeek
Messages: 9
Registered: August 2007
Location: Netherlands
Junior Member
joy_division wrote on Tue, 07 August 2007 15:10
Amersfoort wrote on Tue, 07 August 2007 08:38
Schema's contain tables. Tables contain data in different segments. Indexes are created on the tables.

Schema's with a lot of data will have a lot of indexes. The indexes need to be rebuilded frequently for optimal performance.


Wow, I can't believe I just read that. Are you getting your advice from Don Burleson? You might want to look at Jonethan Lewis' article on indexes http://www.jlcomp.demon.co.uk/indexes_i.html



OK, now I understand what you all talking about (see the four rules under). Thanks for the doc, joy_division

Regards,
Groesbeek


The first (and only) rule of optimization is: "Avoid unnecessary effort". But you have to operate this rule at many levels. In the case of rebuilding indexes, for example, you have four considerations:
• If an index needs constant care and attention, is this a clue that you really need to be fixing a design error.
• If an index is required, it should not be allowed to degenerate so far that the optimizer should stop using it.
• You should not waste resources rebuilding indexes when the performance gain is not worth the effort or risk
• You should not spend excessive amounts of time trying to work out exactly when each index needs to be rebuilt
Previous Topic: EXECUTABLE JOB fail
Next Topic: Oracle 10G ideal parameters
Goto Forum:
  


Current Time: Sat Dec 03 11:45:48 CST 2016

Total time taken to generate the page: 0.04336 seconds