Why is there a clear index for a field in a table sometimes? How can this problem be solved?
A. There are several reasons why indexes are not taken away:
♀You are using the all_rows method at the instance level.
♀Statistical information of your table (the most likely reason)
♀Your table is small. As mentioned above, the Oracle optimizer does not think it is worth indexing.
B. Solution
♀You can modify the optimizer_mode parameter in init <Sid>. ora to rule or choose to restart the database. You can also use the hint mentioned in 4.
♀Delete statistics
SQL> analyze table table_name Delete statistics;
♀It is correct that the table is small and does not take indexes.
Other problems
A. How to check whether a table or index is statistical information?
SQL> select * From user_tables
2 Where table_name = <table_name>
3 and num_rows is not null;
SQL> select * From user_indexes
2 Where table_name = <table_name>
3 and num_rows is not null;
B. if we use the CBO method first, we should update the statistical information of tables and indexes in a timely manner, so as not to generate realistic execution plans.
SQL> analyze table table_name compute statistics;
SQL> analyze index index_name estimate statistics;