I have a table named "T_OBJECTS" and one index on it named "T_OBJECTS_IX1". They are not analyzed now:
TABLE_NAME NUM_ROWS LAST_ANALYZED
-------------- ---------- ---------------
T_OBJECTS
T_OBJECTS_IX1
Now I receive a task to create a second index on this table, I add the compute statistics option :
SQL> CREATE INDEX T_OBJECTS_IX2 ON T_OBJECTS
2 ( OBJECT_NAME ) compute statistics;
Index created.
Let's query the table's statistics data again :
TABLE_NAME NUM_ROWS LAST_ANA
-------------- ---------- --------
T_OBJECTS 555 00:30:54
T_OBJECTS_IX1
T_OBJECTS_IX2 555 00:30:54
Because of the table is analyzed, but another index is not analyzed, so the optimizer choose the wrong execution plan, and get the server in performance trouble. Later I test it on 10g version, the table will not be analyzed. So when you create index with compute statistics option in 9i, you should be really carefully, because table will be analyzed too, while another indexes are not analyzed, it may make the optimizer choose the wrong execution plan.