Using Predicates with LIKE

An index scan may be used for a query with a LIKE predicate. However, there are situations in which a table scan is a better choice for good performance. For example, for LIKE pattern values that start with a wildcard, ALLBASE/SQL needs to scan the whole relation. In this case, a table scan is usually the optimal scan to use. Here are some suggestions for the use of LIKE in predicates:

For a multicolumn index to be chosen by the optimizer, the LIKE predicate must be on the first column in the index if only one column is used. For example, a predicate that includes LIKE C1 might benefit from an index defined on C1,C2 but it would not benefit from an index defined on C2,C1.
Avoid using a LIKE pattern (value or host variable) that starts with a wildcard, since the entire table must be scanned in this case.
For a LIKE predicate on a column that contains clustered data, the performance is improved dramatically when an index is used. Since the data is clustered together, no extra I/O is needed to search for the next tuple.
Avoid using NOT LIKE, since an index plan is not generated for a NOT LIKE predicate.
For LIKE predicates, an index can only be used if all previous columns in the index have values supplied in the predicate. For example, if a multicolumn index exists on (C1, C2, C3) in a table, the following predicate may result in the choice of an index scan:
WHERE C1 = 12 AND C2 = :HostVar AND C3 LIKE 'J%'
However, assuming the same three-column index, the following would not result in the choice of an index scan:
WHERE C1 = 12 AND C3 LIKE 'J%'

Predicates containing LIKE are only optimized if the language of the column is NATIVE-3000.

Using Predicates with LIKE

Technical documentation

» Table of Contents

» Index