PI59348: INEFFICIENT INDEX SELECTION FOR THE INNER TABLE OF A JOIN

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Less than optimal performance is the result when the optimizer
  makes a inefficient index selection for the inner table of a
  nested loop join or hybrid join. This occurs when the inner
  table index is in the same key order as the outer
  composite table index.
  
  
  Additional Keywords:
  
  SQLPERFORMANCE
  SQLACCESSPATH
  SQLNLJ
  NLJ
  SQLHYBRID
  SQLHYBRIDJOIN
  HYBRIDJOIN
  SQLHBJ
  HBJ
  

Local fix

• Optimization Hint
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: DB2 11 for z/OS users of queries with 2      *
  *                 competing indexes for the inner tables.      *
  ****************************************************************
  * PROBLEM DESCRIPTION: A less efficient index may be selected  *
  *                      while another index may have a better   *
  *                      performance for the inner table of a    *
  *                      join.                                   *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  A less efficient index may be selected while another index may
  have a better performance for the inner table of a join. This
  problem could occur when both indexes are very selective and
  the more efficient index has matching predicates and less
  screening predicates.
  
  For example,
  
  SELECT *
  FROM T1, T2
  WHERE T1.C1 = T2.C1
  AND   T1.C2 = T2.C2
  AND   T1.C3 = T2.C3
  AND   T1.C4 = T2.C4;
  
  There are 2 indexes defined on T2: IX1 on (C1,C2,C3), IX2 on
  (C1,C4,C5,C2,C3). If T1 is selected as the leading table and
  joins to T2 with Nested Loop Join, IX2 may be selected because
  it has more screening predicates.
  
  
  Additional Keywords:
  SQLPERFORMANCE SQLACCESSPATH SQLNLJ
  

Problem conclusion

• DB2 has been changed to get a more efficient access path when
  the query may use Nested Loop Join.
  

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:

  UI39082

Modules/Macros

• DSNXOCSC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI39082

     UP16/07/15 P F607

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.