Several reasons for index invalidation

1 using the Not Equal operator (<>,! = )
The following query performs a full table scan even if there is an index in the cust_rating column.
SelectCust_id,cust_name fromCustomerswherecust_rating<> ' AA ';
change the above statement to the following query statement, so that the rule-based optimizer is used instead of the cost-based
Optimizer (smarter), the index will be used.
SelectCust_id,cust_name fromCustomerswherecust_rating< ' AA ' orcust_rating> ' AA '; 
Special Note: By changing the non-equal operator toORcondition, you can use the index to avoid a full table scan.
2 Using is null or is not null
Use is NULLor is  not NULLthe use of indexes is also restricted. BecauseNULLvalues are not defined.
in theSQLused in statementsNULLThere will be a lot of trouble. It is therefore recommended that when a developer makes a table, the columns that need to be indexed
set into not NULL. If the column being indexed exists in some rowsNULLvalue, the index is not used (unless
The index is a bitmap index, about which the bitmap index is discussed later in detail.
3 Using Functions
If you do not use a function-based index, theSQLStatement ofWHEREclause to use for columns that have indexes
function causes the optimizer to ignore these indexes. The following query does not use an index (as long as it is not a function-based
index)
SelectEmpno,ename,deptno fromEMPwheretrunc (hiredate)=' 01-may-81 ';
change the above statement to the following statement so that it can be searched by the index.
SelectEmpno,ename,deptno fromEMPwhereHireDate<(To_date (' 01-may-81 ')+0.9999);
4 Comparing data types that do not match
is also one of the more difficult to find performance issues. Note The following query example, Account_number is a
VARCHAR2type that has an index on the Account_number field.
The following statement performs a full table scan:
Selectbank_name,address,city, State, Zip fromBankswhereAccount_number= 990354;
Oracle can automate thewhereclause becomes to_number (account_number)=990354, this limits
Index is used, the index can be used to change the following query:
Selectbank_name,address,city, State, Zip fromBankswhereAccount_number=' 990354 ';
Special Note: Comparisons between mismatched data types will allow Oracle to automatically limit the use of indexes, even if the
Inquiry ExecutionExplainPlan also doesn't let you know why you did it once-full table scan ‖.

This article is from the "Data Domain" blog, so be sure to keep this source http://charlist.blog.51cto.com/1616209/1697493

Several reasons for index invalidation