Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS

Question

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.

Answer 1

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.

Answer 2

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.