Avoiding Certain Filters

For best performance, avoid the following types of filters:

• Certain difficult regular expressions
• Noninitial substrings

The following sections describe these types of filters and the reasons for avoiding them.

Avoiding Difficult Regular Expressions

The MATCHES and LIKE keywords support wildcard matches, which are technically known as regular expressions. Some regular expressions are more difficult than others for the database server to process. A wildcard in the initial position, as in the following example (find customers whose first names do not end in y), forces the database server to examine every value in the column:

SELECT * FROM customer WHERE fname NOT LIKE '%y'

You cannot use an index with such a filter, so the table in this example must be accessed sequentially.

If a difficult test for a regular expression is essential, avoid combining it with a join. If necessary, process the single table and apply the test for a regular expression to select the desired rows. Save the result in a temporary table and join that table to the others.

Regular-expression tests with wildcards in the middle or at the end of the operand do not prevent the use of an index when one exists.

Avoiding Noninitial Substrings

A filter based on a noninitial substring of a column also requires the database server to test every value in the column, as the following example shows:

SELECT * FROM customer 
   WHERE zipcode[4,5] > '50'

The database server cannot use an index to evaluate such a filter.

The optimizer uses an index to process a filter that tests an initial substring of an indexed column. However, the presence of the substring test can interfere with the use of a composite index to test both the substring column and another column.