IY99606: POSSIBLE POOR QUERY EXECUTION PERFORMANCE WHEN JOINING ON COLUMNS CONTAINING A LARGE PERCENTAGE OF NULLS

DB2 UDB Version 8.1 FixPak 16 (also known as Version 8.2 FixPak 9 )

APAR status

• Closed as program error.

Error description

• The query optimizer may underestimate the cost of a merge join
  (MSJOIN) or nested loop join (NLJOIN) operator when atleast one
  column in the join predicate contains a large percentage of
  nulls.
  
  The following is a simple scenario that can hit this problem:
  
  create table t1 (a int);
  create table t2 (a int);
  create index ix1 on t1 (a);
  
  Data in the tables:
  
  T1 (a)             T2 (a)
            1           1
            2           3
            3           4
            4           5
            5
            6
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
            -
  
  Query: select t2.* from t1,t2 where t1.a=t2.a;
  
  When considering the NLJOIN with T2 on the outer, and T1 (via
  index access) on the inner:
  
   NLJOIN
   /      \
  T2    IX1
  
  the optimizer will underestimate the cost of this NLJN operator
  due to the large number of NULLs in the column T1.a.  This cost
  misestimation may lead to poor performance if it results in the
  optimizer choosing a non-optimal query execution plan.
  

Local fix

• lower optimization level to 0 may avoid the issue.
  

Problem summary

• POSSIBLE POOR QUERY EXECUTION PERFORMANCE WHEN JOINING ON
  COLUMNS CONTAINING A LARGE PERCENTAGE OF NULLS
  

Problem conclusion

• First fixed in DB2 UDB Version 8.1, FixPak 16
  

Temporary fix

• lower optimization level to 0 may avoid the issue.
  

Comments

APAR Information

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

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

  IY99607

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R820 PSY

     UP