Six restrictions that can invalidate Oracle Indexes

Six restrictions that can invalidate Oracle Indexes
The goal of Oracle indexes is to avoid full table scans and improve query efficiency, but sometimes it is counterproductive. For example, if a table contains millions of data records and an index is added to a field, the query performance is not improved. This may be caused by oracle index failure. Oracle indexes have some restrictions. If you violate these index restrictions, oracle will perform a full table scan even if you have added an index, the query performance will not be better than without an index, but may be worse because of the system overhead of the database to maintain the index. The following are the seven restrictions that can invalidate Oracle indexes. 1. no WHERE clause 2. use is null and is not null select... FROM emp WHERE comm is null; index of comm column will be invalid 3. if a function is used in a WHERE clause without a function-based index, the optimizer will ignore these indexes when using a function for an indexed column in The where clause. For example, select * from staff where trunc (birthdate) = '01-MAY-82 '; but if you apply a function to a condition, the index can take effect, if you change the preceding statement to the following statement, you can search by index. Select * from staff where birthdate <(to_date ('01-MAY-82 ') + 0.9999); Note: Oracle still uses indexes for MIN and MAX functions. 4. Use LIKE '% t' for fuzzy search. 5. The operations that are not equal to or not used in the WHERE clause include: <> ,! =, NOT colum> = ?, NOT colum <=? This restriction can be replaced by OR, for example, colum <> 0 ==> colum> 0 OR colum <0 6. equal to and range indexes will not be merged using SELECT emp_id, emp_m, salary_q... FROM emp WHERE job = 'manager' AND deptno> 10 both job AND deptno are non-unique indexes. In this case, oracle does not merge indexes, but uses only the first index. 7. The non-matching data type dept_id is a varchar2 field with an index on this field. However, the following statement performs a full table scan. Select * from dept where dept_id = 900198; this is because oracle will automatically convert the where clause to to_number (dept_id) = 900198, which is equivalent to using functions, thus limiting the use of indexes. The correct syntax is as follows: select * from dept where dept_id = '201312 ';