Help

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":

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:

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:

 

SELECT 

C1 

FROM 

T1 

WHERE 

C1 BETWEEN 'SAMP01' AND 'SAMP99' 

 

Note: 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.