Monday, July 9, 2012

Some factors that can prevent an index from being used in Oracle

The presence of an index on a column does not guarantee it will be used.
  • The optimizer decides it would be more efficient not to use the index. If your query is returning the majority of the data in a table, then a full table scan is probably going to be the most efficient way to access the table.
  • You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'. The solution to this is to use a Function-Based Index.
  • You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
  • You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
  • You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used. Index Skip Scanning in Oracle 9i and above allow indexes to be used even when the leading edge is not referenced.  
  • The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use an INDEX hint.

No comments:

Post a Comment