IBM Support

IZ35464: THE DB2 QUERY OPTIMIZER MIGHT UNDERESTIMATE THE JOIN COST, POTENTIALLY LEADING TO A SUB-OPTIMAL QUERY ACCESS PLAN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • If your SQL statement contains an equality join predicate
    between two tables, such as the one in the WHERE clause in
    the following example
    .
      SELECT ...
      FROM T1, T2 ...
      WHERE ... T1.COLUMN1 = T2.COLUMN2 ...
    .
    and distribution statistics are collected, the optimizer could
    take advantage of the distribution statistics in combination
    with the high2key and low2key statistics during join cost
    computation.
    .
    If the number of distinct values in either COLUMN1 or COLUMN2
    is less than 3, then the optimizer may underestimate the cost
    of the join.
    .
    The number of distinct values is described by the COLCARD column
    in the SYSSTAT.COLUMNS catalog view.
    

Local fix

  • 1) Use an optimizer profile to force the desired access plan, OR
    2) Update the LOW2KEY of the column with the smaller number of
       distinct values to the LOW2KEY of the column with the higher
       number of distinct values.
    

Problem summary

  • see APAR text
    

Problem conclusion

  • Problem first fixed in DB2 UDB 9.1 FixPak 8
    

Temporary fix

  • see APAR text
    

Comments

APAR Information

  • APAR number

    IZ35464

  • Reported component name

    DB2 EDE AIX

  • Reported component ID

    5724N7600

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-10-21

  • Closed date

    2009-10-27

  • Last modified date

    2010-06-02

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

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

    IZ35465 IC61598

Modules/Macros

  • ENG_SQNO
    

Fix information

  • Fixed component name

    DB2 EDE AIX

  • Fixed component ID

    5724N7600

Applicable component levels

  • R910 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
02 June 2010