Avoid accidental table scans

Table scans that occur unintentionally are a major source of poorly performing SQL. Causes include:

a. Missing Index

b. Using “!=“, “<>” or NOT

i. Use inclusive range conditions or IN lists

c. Looking for values that are NULL

i. Use NOT NULL values with a default value

d. Using functions on indexed columns – Function Based Indexes.

Use “functional” indexes in Oracle8i and above

One of the most fundamental SQL tuning problems is the “accidental” table scan. Accidental table scans usually occur when the SQL programmer tries to perform a search on an indexed column that can’t be supported by an index. This can occur when:

Using != (not equal to). Even if the not equals condition satisfies only a small number of rows, Oracle will not use an index to satisfy such a condition. Often, you can re-code these queries using > or IN conditions, which can be supported by index lookups.

Searching for NULLS. Oracle won’t use an index to find null values, since null values are not usually stored in an index (the exception is a concatenated index entry where only some of the values are NULL). If you’re planning to search for values which are logically missing, consider changing the column to NOT NULL with a DEFAULT clause. For instance, you could set a default value of “UNKNOWN” and use the index to find these values.

Using functions on indexed columns. Any function or operation on an indexed column will prevent Oracle from using an index on that column. For instance, Oracle can’t use an index to find SUBSTR(SURNAME,1,4)=’PWAR’. Instead of manipulating the column, try to manipulate the search condition. In the previous example, a better formulation would be SURNAME LIKE ‘PWAR%’. In Oracle8i you can create functional indexes, which are indexes created on functions, providing that the function always returns the same result when provided with the same inputs. This allows you, for instance, to create an index on UPPER(surname).