Skip to Main Content
  • Questions
  • Difference between Rebuild Index & Gather Index Stats

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Nikhil.

Asked: November 03, 2016 - 9:01 am UTC

Last updated: November 03, 2016 - 3:47 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Team,

Can i get any reference on -
1. What is the Difference between Rebuild Index & Gather Index Stats.
2. When to go for Rebuild Index or Gather Index Stats

Regards,
Nikhil

and Chris said...

Index rebuilds and gathering stats are completely separate things!

- Rebuilding an index re-creates it
- Gathering stats gets information about the values it references. e.g. How many values there are, number of different values, etc.

There aren't cases where you'd pick between them.

Rebuilding should happen "almost never":

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2913600659112
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2290062993260
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6601312252730

Normally you wouldn't gather index stats as a standalone process either. Most of the time you'll gather the table stats and do the indexes on it at the same time.

The stats gathering job does this when the table data is "stale". i.e. when the number of changes to the table exceed a certain threshold. In most cases you can use the default (10%). But you may want to tweak this if it isn't working for you.

If you want to know more about this, check out Richard Foote's articles on index stats:

https://richardfoote.wordpress.com/category/index-statistics/

Is this answer out of date? If it is, please let us know via a Comment