Seven limiting conditions that can invalidate Oracle indexes ____oracle

The goal of an Oracle index is to avoid full table scans, which improves query efficiency, but sometimes it backfires.

For example, there is a table with the millions data, indexed to a field, but performance did not improve the query, this may be due to the failure of the Oracle index. Oracle indexes have some limitations, and if you violate these index restrictions, Oracle will perform a full table scan, even if you have indexed them, and the query's performance will not improve without indexing, but it may be less performance due to the system overhead of database maintenance indexing. The following are the seven limiting conditions that summarize the failure of the Oracle index.

1. There is no WHERE clause

2. Use is null and is NOT NULL

SELECT ... From EMP WHERE comm is NULL; The index of the Comm column will fail

3. Using functions in the WHERE clause

If you do not use a function based index, the optimizer ignores these indexes when using functions for columns that are indexed in the WHERE clause. For example:

SELECT * FROM staff where trunc (birthdate) = ' 01-may-82 ';

But the application of the function on the condition, the index can be effective, the above statement to the following statement, you can search through the index.

SELECT * FROM staff where birthdate < (to_date (' 01-may-82 ') + 0.9999);

Note: For the MIN, MAX function, Oracle still uses the index.

4. Use like '%T ' for fuzzy query

5. Use not equal to operation in 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 index will not be used in combination

SELECT emp_id, Emp_m, salary_q ... From EMP WHERE job= ' manager ' and deptno>10

Both the job and the Deptno index are not unique, and Oracle will not merge the index, which will only use the first index.

7. Compare mismatched data types

DEPT_ID is a VARCHAR2 type field that has an index on this field, but the following statement performs a full table scan.

SELECT * FROM dept where dept_id = 900198;

This is because Oracle automatically converts the WHERE clause to To_number (dept_id) = 900198, which is equivalent to using a function, which limits the use of the index. The correct wording is as follows:

SELECT * FROM dept where dept_id = ' 900198 ';