From the network http://blog.chinaunix.net/uid-23622436-id-3184356.htmlreason for Oracle index failure
Category: Oracle
1) No query criteria, or the query condition is not indexed
2) The boot column is not used on the query condition
3) The number of queries is the large majority of the table, should be more than 30%.
4) The index itself fails
5) The query condition uses the function on the index column (see 12)
6) Query for small tables
7) prompt not to use index
8) statistical data is untrue
9) The CBO calculates that it takes too much to go through the index. In fact, it also contains the above situation, this refers to the table occupies a block to the peso small.
10) Implicit conversions cause an index to fail. This should be taken seriously. It is also a common mistake in development. Because the table field TU_MDN is defined as VARCHAR2 (20),
However, the field is passed to Oracle as the number type in the query, which causes the index to be invalidated.
Example of error: SELECT * from Test where tu_mdn=13333333333;
Right Example: SELECT * from Test where tu_mdn= ' 13333333333 ';
11) Operations on indexed columns result in index invalidation, and I mean operations on indexed columns including (+,-,*,/,!, etc.)
Example of error: SELECT * from Test where id-1=9;
The correct example: SELECT * from Test where id=10;
12) The use of Oracle Intrinsics causes the index to fail. For this scenario, you should create a function-based index.
Example of error: SELECT * from Test where round (id) = 10;
Description, the index of the ID has no effect at this point in the correct example: first establish the function index,
CREATE INDEX TEST_ID_FBI_IDX on test (round (ID));
Then select * FROM Test where round (id) = 10; At this time the function index functioned 1,<> 2, a separate >,<, (sometimes used, sometimes not)
3,like "%_" percent semicolon in front.
4, the table is not analyzed.
5, the index column that is not the first position in the composite index is referenced separately.
6, when the character field is a number, no quotation marks are added in the Where condition.
7. Perform an operation on an indexed column. The function index needs to be established.
8,not in, not exist.
9, when the variable takes the Times variable, and the table field takes a date variable. or the opposite.
10, the index is invalid.
11, cost-based analysis (Oracle because the cost of walking the table is smaller): Query the small table, or the return value of about 10% or more
12, sometimes it's all taken into account, but it's not going to be indexed, drop the test from the build
13,b-tree index is null does not go, is not NULL will go, bitmap index is null,is not NULL will go
14, the federated index is not null as long as the indexed column is established (in no particular order) will go,
In null must be used in conjunction with the first column of the index, when the index first position condition is NULL,
Other indexed columns can be is null (but must be when all columns satisfy is null),
or = a value; When the first position of the index is = one value, the other indexed columns can be any case (including is NULL = a value).
In both cases, the index will go. Other things will not go. Transferred from: http://blog.csdn.net/zcywell/article/details/7470103
Oracle Index Failure Reason
