How does Oracle make the index invalid?

Causes of oracle index failure: 1. Not using the WHERE clause; 2. Using "IS NULL" and "IS NOT NULL"; 3. Using functions in the WHERE clause; 4. Using LIKE for fuzzy Query; 5. Use the inequality operation in the WHERE clause; 6. Compare unmatched data types, etc.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How oracle will make the index invalid

The goal of Oracle index is to avoid full table scan and improve query efficiency, but sometimes it is counterproductive.

For example, there are millions of pieces of data in a table, and an index is added to a certain field, but the query performance does not improve. This may be caused by the failure of the Oracle index. Oracle indexes have some restrictions. If you violate these index restrictions, even if you have added an index, Oracle will still perform a full table scan. The query performance will not be improved compared to not adding an index. Instead, it may be due to the database The system overhead of maintaining the index results in worse performance. The following is a summary of the seven constraints that can cause Oracle indexes to fail.

1. No WHERE clause

2. Use IS NULL and IS NOT NULL

SELECT ... FROM emp WHERE comm IS NULL; The index on the comm column will be invalid

3. Use the function in the WHERE clause

If no function-based index is used, then the where clause When using functions on indexed columns, the optimizer will ignore these indexes. For example:

select * from staff where trunc(birthdate) = '01-MAY-82';

But when the function is applied to the condition, the index can take effect. If you change the above statement to the following statement, you can search through the index.

select * from staff where birthdate < (to_date(&#39;01-MAY-82&#39;) + 0.9999);

Note: Oracle still uses indexes for MIN, MAX functions.

4. Use LIKE '%T' for fuzzy query

5. Use the inequality operation in the WHERE clause

Not equal to operations include: <>, !=, NOT colum >= ?, NOT colum <= ?

This restriction can be replaced by OR, for example: colum <> 0 ===> colum>0 OR colum<0

6. Equal and range indexes will not be combined.

SELECT emp_id, emp_m, salary_q ... FROM emp WHERE job='manager' AND deptno>10

job and deptno are both non-unique indexes. Oracle will not merge indexes under this condition, it will only use the first index.

7. Comparison does not match the data type

dept_id is a varchar2 field. There is an index on this field, but the following statement will perform a full table scan.

select * from dept where dept_id = 900198;

This is because Oracle will automatically convert the where clause into to_number(dept_id)=900198, which is equivalent to using a function, which limits the use of indexes. The correct way to write it is as follows:

select * from dept where dept_id = '900198';

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How does Oracle make the index invalid?. For more information, please follow other related articles on the PHP Chinese website!