 |
» |
|
|
|
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
n-computer. |
