Do I need to run gather_table_stats every time I create an index in order for the Oracle optimizer to use it?

Question

I saw some examples where indexes were being created. Afterwards the following was executed:

exec dbms_stats.gather_table_stats(...)

Is this necessary for Oracle to pay attention to the index? I think stats are gathered every night (?) but there have been situations where I created an index was was disappointed by the explain plans that followed. Maybe I'm missing a step?

Answer 1

It depends on the version of Oracle.

In versions prior to 9i, you had to explicitly gather statistics after creating an index before the cost-based optimizer would have any realistic chance of using it.

In 9i, Oracle added the COMPUTE STATISTICS clause to the CREATE INDEX statement. That allowed you to gather statistics on the index as part of the index creation process. If you didn't specify COMPUTE STATISTICS, you still had to manually gather the statistics before the CBO would be likely to consider it.

In 10g, the default behavior changed and Oracle would automatically compute the statistics on the index when you created it without requiring you to specify COMPUTE STATISTICS. Out of force of habit or because they're just updating older example code, people will often still include the GATHER_INDEX_STATS call in examples they post.

In 10g and later, there is a background job that is created by default that gathers statistics on objects that are missing statistics and objects whose statistics are stale at night. DCookie's explanation of the 10g job is spot on. Oracle changed how the job was set up in 11g but it's still essentially doing the same things.