Observation: Non-indexable LIKE Predicate

You coded a non-indexable LIKE predicate. Db2 will not use an index when you supply a wildcard character as the first character in a LIKE predicate. The DB2 optimizer can use a matching index scan when the leading characters of a search string are specified in the LIKE predicate.

Regardless of whether DB2 considers the LIKE predicate indexable, if the set of possible values is relatively small, consider recoding the LIKE predicate with either IN or BETWEEN, both of which are generally more efficient alternatives.

The example below includes a LIKE predicate to return all values of C1 that begin with "SAMP":

Example

SELECT
C1
FROM
T1
WHERE
C1 LIKE 'SAMP%'

If C1 contains only five possible values, you could recode that statement using IN predicate, as shown below:

Example

SELECT
C1
FROM
T1
WHERE
C1 IN ('SAMP01', 'SAMP02', 'SAMP03', 'SAMP04', 'SAMP05')

Similarly, if C1 can contain only a finite set of values, in this example, "01" through "99", you can recode the statement using BETWEEN predicate, as shown below:

Example

SELECT
C1
FROM
T1
WHERE
C1 BETWEEN 'SAMP01' AND 'SAMP99'

Important

DB2 can determine whether the first character in a host variable contains a wildcard character. However, since DB2 can not know the actual content of the host variable until run time, the Db2 optimizer indicates only that an index might be used. The following defines the access path chosen by DB2 at run time based on the value in host variables:

If the host variable value begins with a valid, non-wildcard character, DB2 uses matching index scan.
If the host variable value begins with "%" or " " and a clustering index exists with a cluster ratio greater than or equal to eighty percent, DB2 uses a non-matching index scan; if the cluster ratio is less than eighty percent, DB2 uses a tablespace scan.

Observation: Non-indexable LIKE Predicate

BMC AMI iStrobe Web 23.04

On this page