PM56785: EFFICIENT CORRELATED FORM NOT CHOSEN FOR SUBQUERY WHEN A CORRELATION PREDICATE COLUMN IS UNIQUE IN THE OUTER QUERY BLOCK

APAR status

• Closed as duplicate of another APAR.

Error description

• Consider the following query and indexes...
  
  SELECT ...
  FROM T1
  INNER JOIN T2
    ON T1.C2 = T2.C2
  WHERE T1.C1 = ?
    AND EXISTS (
      SELECT 1
      FROM T3
      WHERE T3.C3 = T2.C3
    )
  
  Unique Index IX1 on T1(C1)
  Unique Index IX2 on T2(C3)
  Index IX3 on T3(C3)
  
  Access to T1 is guaranteed to return a single row due to the
  local predicate on unique column C1.  Since the subquery cannot
  result in additional rows, join sequence T1->(noncor subq) is
  also guaranteed one row.  Join sequence T1->(noncor subq)->T2 is
  also guaranteed one row since there is a unique index on
  correlated column T2.C3.
  
  In this specific scenario, DB2 may incorrectly choose the
  non-correlated form instead of the correlated form of the
  subquery even if the correlated form is less expensive.
  
  SQLACCESSPATH SQLPERFORMANCE SQLSUBQUERY SQLNONCORRSUBQ
  SQLCORRSUBQ
  

Local fix

• Disable zparm OPTXQB and code the subquery as correlated.
  

Problem summary

Problem conclusion

Temporary fix

Comments

• This performance problem is a subset of a larger problem
  in the way DB2 determines the expected sort order of an
  access path.
  The complete solution to this problem is being delivered
  via APAR PM57388.
  

APAR Information

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

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

Fix information

Applicable component levels