LI73096: INLIST TO INDEX OR PREDICATE TRANSFORMATION NOT CONSIDERED WHEN PREDICATE IS PART OF ON CLAUSE FOR OUTER JOIN.

DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• In a query which has an outer join containing an in list
  predicate, query rewrite will not consider converting this
  inlist to an index or predicate.
  This can lead to suboptimal performance plans.
  
  e.g. the following construct could be liable to this behaviour :
  
  select tab1.c, Q1.f
  from tab1
   right outer
  join
    ( select d, e , f from tab2
      where d in ( 1 , 2 , 3, 4 , 5, 6, 7, 8 , 9, 10 ) ) as Q1
      on ( Q1.d = tab1.a )
     and tab1.a in ( 1 , 2 , 3, 4 , 5, 6, 7, 8 , 9, 10 )
  
  Without this apar fix, the plan will result in a TBSCAN operator
  against tab2 in order to apply the inlist predicate on column d.
  

Local fix

• Applying the inlist 2 join transformation may help at optlevel
  7.
  

Problem summary

• Users affected : Users with queries that have inlists in
  subqueries Problem description : suboptimal performance
  Problem Summary : This apar will ensure a more optimal execution
  plan
  

Problem conclusion

• First fixed in DB2 UDB Version 9, Fixpak 5
  

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:

  LI73109

Fix information

• Fixed component name

  DB2 EDE LINUX

• Fixed component ID

  5724N7604

Applicable component levels

• R910 PSY

     UP