Suppressing indexes unintentionally

Unintentionally suppressing indexes is one of the most common mistakes made by an inexperienced developer. SQL contains many traps that cause indexes not to be used.

• Using the NOT EQUAL Operators: <>, !=
  Indexes can only be used to find data that exists within a table. Whenever the NOT EQUAL operators are used in the WHERE clause, indexes on the columns being referenced cannot be used.
  
• Using IS NULL or IS NOT NULL
  
  When you use IS NULL or IS NOT NULL in your WHERE clauses, index usage is suppressed because the value of NULL is undefined

• Using LIKE
  
  LIKE, in some cases, uses an index, while in others, it does not. The most common uses of LIKE are LIKE ‘%somevalue%’ or LIKE ‘somevalue%’ (where the % is only at the end of the search string). Only one of these cases uses the index—the case where the value is first, LIKE ‘somevalue%’.
   
•  Using Function
  
  Unless you are using function-based indexes, applying functions on indexed columns in the WHERE clause of a SQL statement causes the optimizer to bypass indexes. Some of the most common functions are TRUNC, SUBSTR, TO_DATE, TO_CHAR, and INSTR