Several scenarios where Oracle does not walk the index

Other reasons to not walk the index:

1. Set up a composite index, but the query predicate does not use the first column of the combined index, there is an index SKIP scan concept here.

2. Index on table column containing null value, when using SELECT COUNT (*) from table does not use index.

3. Indexes are not used when functions are used on indexed columns, and only function indexes can be established if you must use an index.

4. The index is not used when the indexed column is implicitly type-converted. such as: SELECT * from t where Indexed_column = 5, while the Indexed_column column is indexed but the type is character type, Oracle generates an implicit type conversion, the converted statement is similar to the select * from T where T O_number (Indexed_column) = 5, the situation is similar to CASE3 when the index is not gone. Date conversion also has a similar problem, such as: SELECT * from T where trunc (date_col) = Trunc (sysdate) where Date_col is the index column, so that the write does not go index, can be rewritten as a select * from T where date _col >= trunc (sysdate) and Date_col < Trunc (sysdate+1), this query walks the index.

5, not all cases using the index will speed up the query, the full scan table is sometimes faster, especially when the amount of data in the query for the entire table is large (about 20% greater than the total), because it uses a multi-block read, When the Oracle optimizer does not choose to use an index without immediately forcing it, it is sufficient to prove that using an index does query faster when you use a forced index.

6. <>

7, like '%DD ' percent semicolon in front.

8, not in,not exist

Several scenarios where Oracle does not walk the index