•
A common DBA mistake is creating an index and then
forgetting to compute statistics on the index. When an
index has no statistics, Oracle9i and earlier will not see
the index. Hence you have to issue two commands:
Create index cust_name_idx;
EXEC DBMS_Stats.Gather_Index_Stats('fred', 'cust_name_idx');
Starting in Oracle 9i, we have a “compute statistics
clause:
Create index cust_name_idx COMPUTE STATISTICS;
In Oracle 10g, we don’t need the “compute statistics clause
anymore, and index statistics are
automatically collected when the object is created, and
refreshed when they become stale. We also have the
Oracle 10g dynamic sampling utility.
