HP 3000 Manuals

ALLBASE/SQL Performance Guidelines

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 (in MPE/iX) or n-computer (in HP-UX).

MPE/iX 5.0 Documentation