IBM Support

IC74857: DB2 OPTIMIZER MAY CHOOSE THE LESS OPTIMAL PLAN WHERE SUBQUERY HAS DISTINCT KEYWORD

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Starting DB2 v9.7, relatively large query with multiple UNION
    ALL clauses and subquery with DISTINCT keyword may have slow
    performance than previous versions. This is a very uncommon
    scenario which Optimizer has missed the opportunity to choose
    the best plan.
    User can check for following conditions
    
    -There is a fairly big cartesian join below the outer join in
    the access plan.
     DB2 may push down a join (in this case a subquery with a GROUP
    BY) that can magnify the size of the cartesian join which can
    cause a huge SORT to remove duplicates.
    For example, In previous version same query would have access
    plan which shows:
    
    
                      join (predicate between T1 and T3)
                     /     \
             outer join     GB over T3
            /          \
       MEDIUM SORT     T2
          /
     cartesian join
    /             \
    XXX            T1
    
    
    Where in v9.7, following access plan is chosen which degrades
    the performance.
    
               outer join
              /          \
          BIG SORT        T2
            /
          join (predicate between T1 and T3)
         /    \
       join    T1
      /   \
    XXX    GB over T3
    
    
    The fix for this APAR will enhance this so that DB2 Optimizer
    will choose a much smaller SORT to remove duplicates and not
    push down the subquery if the target is a cartesian join
    
                      join (predicate between T1 and T3)
                     /     \
             outer join     GB over T3
            /          \
     cartesian join    T2
    /             \
    XXX            SMALL SORT to remove duplicates from T1
    

Local fix

  • Rewrite the query to replace DISTINCT by GROUP BY clause.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All.                                                         *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Problem Description above.                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 5.                       *
    ****************************************************************
    

Problem conclusion

  • First Fixed in Version 9.7 Fix Pack 5.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC74857

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-03-04

  • Closed date

    2011-12-23

  • Last modified date

    2011-12-23

  • 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

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC74857

Modified date: 23 December 2011