IC69311: OPTIMIZER CHOOSES SLOW EXECUTION PLAN FOR LIKE/MATCHES USING WILDCARD FOLLOWED BY BLANKS

APAR status

• Closed as program error.

Error description

• The optimizer might choose a slow execution plan when the WHERE
  clause contains a LIKE/MATCHES condition which uses a wildcard
  followed by blanks.
  
  Removing the blanks or using the wildcard character instead of
  blanks will solve the problem.
  
  Slow execution plan:
  
  select tab1.col1, tab2.col1
  from tab1, tab2
  where
  tab1.id=tab2.id and
  tab2.col1[1,4] matches "*    " and
  
  tab2.col1[5,6] matches "* ";
  
  
  
  Fast execution plan:
  
  select tab1.col1, tab2.col1
  from tab1, tab2
  where
  tab1.id=tab2.id and
  tab2.col1[1,4] matches "*" and
  
  tab2.col1[5,6] matches "*";
  
  
  or
  
  select tab1.col1, tab2.col1
  from tab1, tab2
  where
  tab1.id=tab2.id and
  tab2.col1[1,4] matches "****" and
  
  tab2.col1[5,6] matches "**";
  

Local fix

• Remove the blanks after the wildcard character.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All 11.50 users.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * A query with MATCHES or LIKE predicate where the pattern to  *
  * be matched is composed of all wildcard characters followed   *
  * by blank (spaces), e.g., col MATCHES '*  ' or col LIKE '%    *
  * ', may have sub-optimal query plan due to wrong selectivity  *
  * estimate for such predicates.                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.50.xC8.                                        *
  ****************************************************************
  

Problem conclusion

• The problem is fixed in 11.50.xC8.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSN

     UP