Efficiency of Filters

The efficient use of queries depends in part upon the way you construct filter expressions, and in part upon the way the administrator constructs table indexes. Programmers and administrators can use these rules of thumb to help promote efficiency and high performance.

Programmers: consult your data base administrator for information about the definition of indexes.

Keys and Indexes

Rule of Thumb: Construct filter expressions in which every conjunct refers to a key or index.

A filter expression that does not refer to a key or index results in a full table scan, which is inefficient.

A compound filter expression, which combines conjuncts using AND or OR, results in a full table scan for each conjunct that does not refer to a key or index.

Left-Most Columns

Rule of Thumb: Construct filter expressions that reference the left-most columns of an index using the predicates =, ==, <=, >=, <, >, IN, or BETWEEN.

When an index includes more than one column, the administrator has defined them in a specific order: from left to right. Queries with filter expressions that refer to the left-most column of an index can be more efficient than filter expressions that skip the left-most column and instead refer only to columns to its right. Similarly, queries with filter expressions that refer to the left-most two columns can be even more efficient. Queries can achieve maximum efficiency when they use filter expressions that refer to all the columns of an index.

In contrast, omitting the left-most column from the filter expression results in a full table scan, which is the least efficient.

The order in which the columns appear within the filter expression does not affect efficiency. Only the order of columns when defining the index matters.

Avoid Left-Most NOT

Rule of Thumb: Do not construct filter expressions that reference the left-most columns of an index using the predicates NOT, IS NOT, !=, <>, IS, ISNULL, IS NULL, NOTNULL, NOT NULL, and IS NOT NULL.

In contrast to the rule of left-most columns, filter expressions that reference the left-most columns with these operators have the opposite effect: to guarantee a full table scan, which is the least efficient.

The presence of the predicate IS in this list could be counterintuitive. See the next rule.

However, this rule does not imply that predicates in the NOT family are always inefficient. For example, a query can still be efficient if it obeys the left-most columns rule and also tests columns further to the right using NOT. For example, if the administrator defined an index on the columns lastname and firstname, then this filter expression can be efficient: 
lastname='Smith' and firstname IS NOT 'Dan'

Avoid IS

Rule of Thumb: Use the predicates = or ==, rather than IS.

Even though the predicates =, ==, and IS are semantic synonyms, the behavior of IS differs dramatically. Namely, IS guarantees a full table scan, which is the least efficient.

Bound Ranges from Both Ends

Rule of Thumb: When using the predicates > or >=, which specify a lower limit on a column's value, also include the opposite predicates, < or <=, to specify an upper limit on the same column.

A query searches an index from its lower limit to its upper limit. If you omit the upper limit, the query continues searching to the end of the index. If you omit the lower limit, the query begins with the first row of the index.