Oracle database index failure, oracle database index

Oracle database index failure, oracle database index

Today, a colleague suddenly asked me why my index failed. To be honest, there are many reasons for failure:

However, if the same SQL statement can be used before, indexes cannot be used now. The following are the main cases:

1. As the table grows, the where condition produces too much data, greater than 15%, leading to index failure (which will cause CBO to calculate the index cost more than the entire table)

2. If the statistical information is invalid, You need to collect the statistical information again.

3. If the index itself is invalid, re-indexing is required.

The following are some reasons why indexes are not used:

Index failure
1) No query conditions, or no index is created for the query conditions.
2) No Bootstrap column is used for query conditions.
3) The number of queries is the majority of large tables, which should be more than 30%.
4) The index itself is invalid.
5) Use a function for the query condition in the index column (see figure 12)
6) small table Query
7) The system prompts that no index is used.
8) the statistical data is untrue.
9) CBO computing takes the case that the indexing cost is too large. In fact, it also includes the above situation. Here, the block occupied by the table is smaller than the index.
10) implicit conversion causes index failure. This should be paid attention to. It is also a common mistake in development. Because the table field tu_mdn is defined as varchar2 (20 ),
However, this field is passed to Oracle as the where condition of the number type during query, which will cause index failure.
Example of an error: select * from test where tu_mdn = 13333333333;
Correct example: select * from test where tu_mdn = '201312 ';
11) If you perform an operation on the index column, the index becomes invalid. The operations I refer to on the index column include (+ ,-,*,/,! )
Example of an error: select * from test where id-1 = 9;
Example: select * from test where id = 10;
12) using Oracle internal functions causes index failure. In this case, you should create a function-based index.
Example of an error: select * from test where round (id) = 10;
Note: At this time, the id index does not take effect. The correct example is: first, create a function index,
Create index test_id_fbi_idx on test (round (id ));
Then select * from test where round (id) = 10; then the function index takes effect 1, <> 2, separate>, <, (sometimes used, sometimes not)
3, like "% _" percent before.
4. The table is not analyzed.
5. reference the index column not at the first position in the composite index separately.
6. When the numeric field is a number, no quotation marks are added to the where condition.
7. Create a function index for the index column.
8, not in, not exist.
9. When the variable uses the times variable and the table field uses the date variable, or the opposite is true.
10. The index is invalid.
11. cost-based cost analysis (oracle costs less because it takes a full table): Query small tables, or return a value of more than 10%.
12. Sometimes I think about it, but I just don't go through the index.
13. The B-tree index is null and does not go. is not null and bitmap index is null and is not null.
14. If the joint index is not null, it will go as long as the index column is created (in no particular order,
When in is null, it must be used with the first column of the index. When the first position of the index is null,
Other indexed columns can be "is null" (but must meet the condition that all columns are "is null ),
Or = A value. When the first position of the index is = a value, other index columns can be in any situation (including is null = A value ),
In both cases, the index will go. In other cases, it will not go.