Summary of the causes for Oracle execution plan not going indexed

The reason why Oracle execution plan does not go through the index is summarized in Oracle Database Operations. Why does one field in a table obviously have an index? When we observe that the execution plan of some languages does not go through the index? How can this problem be solved? This article mainly introduces this part. Next let's take a look at it. There are several reasons for not going to index: you are using the all_rows method at the Instance level. Your table's statistical information (the most likely cause) is very small. As mentioned above, the Oracle optimizer does not think it is worth indexing. Www.2cto.com solution: Modify the OPTIMIZER_MODE parameter in init. ora, change it to Rule or Choose, and 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 a 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. Www.2cto.com 5. Not all cases will accelerate the query speed when using indexes. full scan table is sometimes 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 an index, do not immediately force the use of the index, 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.