IBM Support

LI74209: EXECUTE INNER JOIN PREDICATE BEFORE OUTER JOIN PREDICATE IN ONE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • The filtering inner join predicate is desirable to be evaluated
    before the outer join predicate from query performance point of
    view. This APAR fix aims at such query that has one fact table
    joins with multiple dimension tables. The join types are mixed
    with INNER JOIN and LEFT OUTER JOIN or RIGHT OUTER JOIN. The
    following INNER JOIN predicate will be evaluated earlier than
    OUTER JOIN
    
    "fact_table.col1 = dim_tablei.col1 and <DATE type literal>
    between dim_tablei.col2 and dim_tablei.col3"
    
    if dim_tablei has unique index on dim_table1(col1, col2, col3)
    and there exists no pair of rows sharing the same
    (col1, col2). col2 and col3 must be of DATE type.
    

Local fix

Problem summary

  • The filtering inner join predicate is desirable to be
    evaluated
    before the outer join predicate from query performance point of
    view. This APAR fix aims at such query that has one fact table
    joins with multiple dimension tables. The join types are mixed
    with INNER JOIN and LEFT OUTER JOIN or RIGHT OUTER JOIN. The
    following INNER JOIN predicate will be evaluated earlier than
    OUTER JOIN
    
    "fact_table.col1 = dim_tablei.col1 and <DATE type literal>
    between dim_tablei.col2 and dim_tablei.col3"
    
    if dim_tablei has unique index on dim_table1(col1, col2, col3)
    and there exists no pair of rows sharing the same
    (col1, col2). col2 and col3 must be of DATE type.
    

Problem conclusion

  • This issue is fixed in v9.5 fixpack 5, but we recommend
    upgrading to V9.5 fixpack 6 due to a regression that this
    fix introduces in fixpack 5 but is now fixed in fixpack 6.
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI74209

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2009-03-02

  • Closed date

    2010-08-25

  • Last modified date

    2010-08-25

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

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

    IC61604

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
25 August 2010