Home » RDBMS Server » Performance Tuning » Reorganizing Vs Rebuilding Indexes (Oracle 9i)
Reorganizing Vs Rebuilding Indexes [message #319711] Mon, 12 May 2008 14:51 Go to next message
riki
Messages: 12
Registered: April 2008
Location: ny
Junior Member
Does any body have any good recommendations for maintenace plans?
Here are few questions I have.

When should indexes be re-indexed?

What should be done first? Reorganize indexes or rebuild indexes?

How often backups should be done and what kind?

How often should database statistics be updated?

Do database statistics need need to be updated on system databases?

How often should a database integrity check be done?

How long should history be kept for?

What is a good order for tasks should be done?



Any input would be great.

Regards,
riki.

Report message to a moderator

Re: Reorganizing Vs Rebuilding Indexes [message #319712 is a reply to message #319711] Mon, 12 May 2008 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When should indexes be re-indexed?

Never (but in some exceptional cases like purge).

Quote:
Do database statistics need need to be updated on system databases?

Of course.

Quote:
How often should a database integrity check be done?

If you mean consistent: Never, a database is ALWAYS consistent with the constraints you gave.
If you mean against corruption, this is done by backup if you use RMAN. Otherwise use dbverify from time to time.

Quote:
How long should history be kept for?

History of what?

Regards
Michel

Report message to a moderator

Re: Reorganizing Vs Rebuilding Indexes [message #319713 is a reply to message #319711] Mon, 12 May 2008 15:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> Reorganizing Vs Rebuilding Indexes
What is the difference between the two & why would either need to be done?

Report message to a moderator

Re: Reorganizing Vs Rebuilding Indexes [message #319726 is a reply to message #319713] Mon, 12 May 2008 16:12 Go to previous messageGo to next message
riki
Messages: 12
Registered: April 2008
Location: ny
Junior Member
Reorganize simply re-sorts the index in place. It will not help fragmentation.

Rebuild always rebuilds the entire index, no matter how much fragmentation there is.

My Question was how to decide which is what I need to do to make my query run faster in terms of tuning

Regards,
riki.

Report message to a moderator

Re: Reorganizing Vs Rebuilding Indexes [message #319728 is a reply to message #319711] Mon, 12 May 2008 16:22 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Reorganize simply re-sorts the index in place. It will not help fragmentation.
What is sample SQL that does this?
Why/how would the index ever be not sorted?
Having a specific order is the only purpose of an index.

>Rebuild always rebuilds the entire index, no matter how much fragmentation there is.
What is sample SQL that does this?

>My Question was how to decide which is what I need to do to make my query run faster in terms of tuning
By running controlled tests & benchmarking results.

Report message to a moderator

Previous Topic: Tuning Error with Order by clause
Next Topic: Tuning
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 27 19:51:09 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.