Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS:

Exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'USER/SCHEMA', tabname => 'MYTABLE', cascade=> true, degree=> 8);

The "CASCADE" attribute was defined as: Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes.

So my question is, should I run the REBUILD of my indexes prior to GATHER_TABLE_STATS, as I'm assuming gathering the index stats and rebuilding them IS NOT the same thing, or is it?

At the moment, I run dbms_index_utl.build_table_indexes to rebuild my indexes followed by the GATHER_TABLE_STATS, I just don't know if it's necessary, mind you my explain plan seems to indicate it is, otherwise, unless I add a hint to my query, the index won't get used.

share|improve this question

migrated from stackoverflow.com Aug 2 '12 at 21:03

This question came from our site for professional and enthusiast programmers.

2 Answers 2

Gathering statistics and rebuilding indexes are two completely separate things.

  • It is exceedingly rare that an index in Oracle needs to be rebuilt so any process that is regularly rebuilding an index is highly suspect. I strongly suspect that rebuilding the indexes is unnecessary. And I would strongly suggest spending some quality time reading through Richard Foote's Index Internals - Rebuilding the Truth to understand why rebuilding indexes is so rarely needed and those few cases where it is necessary.
  • You do need to gather statistics on tables and indexes when there are substantial changes in data volumes or distributions in order to give the optimizer the information it needs in order to determine the appropriate query plan. If you create an index on an empty table and subsequently insert millions of rows into the table, you'll need to gather statistics to tell the optimizer that the table is no longer 1 empty extent. Unless you disabled the automatic job, Oracle will also automatically gather statistics on any objects that have changed substantially since the last time statistics were gathered during the early morning hours every day.
share|improve this answer
    
Sorry, I might have left out some details. In some cases, I render the index "unusable" prior to populating given the volume of data. I read that it will help with the insert performance, where in these cases, I don't have a choice but to rebuild my index to enable, would that be correct? I don't rebuild the indexes on empty tables, but rather when a substantial amount of data change has been performed. I guess you pretty much answered my question though as far as whether the "Rebuild" is different than the gather index stats though. Thanks! –  denisb Jul 24 '12 at 18:25

If you rebuilt all of your indexes you should not have to gather statistics on the indexes. Since 10g, the COMPUTE STATISTICS option was deprecated because "Oracle Database now automatically collects statistics during index creation and rebuild.".

Gathering statistics on your table may be helpful, but you can save some time by setting cascade => false.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.