| Reindex database [message #256655] |
Mon, 06 August 2007 04:39  |
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 #256960 is a reply to message #256662] |
Tue, 07 August 2007 05:06  |
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 |
pablolee
Messages: 2882
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 #256987 is a reply to message #256976] |
Tue, 07 August 2007 05:44 |
pablolee
Messages: 2882
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 |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
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 |
pablolee
Messages: 2882
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 #257033 is a reply to message #256655] |
Tue, 07 August 2007 07:38 |
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 |
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 #257045 is a reply to message #257033] |
Tue, 07 August 2007 08:04 |
pablolee
Messages: 2882
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 |
joy_division
Messages: 4963
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 #257137 is a reply to message #257047] |
Tue, 07 August 2007 11:28 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
