Oracle does not go through the index.

Oracle does not go through the index.

In Oracle Database Operations, why do some fields in a table obviously have indexes? How can this problem be solved? This article mainly introduces this part. Next let's take a look at it.

There are several reasons why indexes are not used:

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.

Solution:

You can modify the OPTIMIZER_MODE parameter in init. ora to Rule or Choose to restart the database. You can also use the Hint mentioned in 4.

Other reasons for not leaving the index:

1. Create a composite INDEX, but the query predicate does not use the first column of the composite INDEX. Here there is an index skip scan concept.

2. Create an index on the table column that contains null values. The index is not used when select count (*) from table is used.

3. indexes are not used when you use a function on an index column. If you must use an index, you can only create a function index.

4. indexes are not used for implicit type conversion of indexed columns. for example, select * from t where indexed_column = 5, while the indexed_column creates an index but the type is partial, Oracle will generate implicit type conversion, the converted statement is similar to select * from t where to_number (indexed_column) = 5. In this case, the case that no index is taken is similar to case3. there are similar issues with date conversion, such as: select * from t where trunc (date_col) = trunc (sysdate) where date_col is the index column, so that the write will not go through the index, you can write it as select * from t where date_col> = trunc (sysdate) and date_col <trunc (sysdate + 1). This query takes the index.

5. Not all cases will accelerate the query speed when using indexes, and full scan table may be faster, especially when the queried data volume accounts for a large proportion of the entire table, because full scan table uses multiple reads, when the Oracle optimizer does not choose to use the index, do not immediately force the use, to fully prove that the use of the index is indeed faster query and then use the forced index.

6. <>

7. like '% dd' before percent.

Here is a summary of the related knowledge about the reason why Oracle execution plans do not go indexed. I hope this introduction will help you gain some benefits!

 

Java enterprise-level general permission security framework source code SpringMVC mybatis or hibernate + ehcache shiro druid bootstrap HTML5

 

[Download java framework source code]