IBM Support

LI73109: INLIST TO INDEX OR PREDICATE TRANSFORMATION NOT CONSIDERED WHEN PREDICATE IS PART OF ON CLAUSE FOR OUTER JOIN.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • In a query which has an outer join containing an in list
    predicate, query rewrite will not consider converting this
    inlist to an index or predicate.
    This can lead to suboptimal performance plans.
    
    e.g. the following construct could be liable to this behaviour :
    
    select tab1.c, Q1.f
    from tab1
     right outer
    join
      ( select d, e , f from tab2
        where d in ( 1 , 2 , 3, 4 , 5, 6, 7, 8 , 9, 10 ) ) as Q1
        on ( Q1.d = tab1.a )
       and tab1.a in ( 1 , 2 , 3, 4 , 5, 6, 7, 8 , 9, 10 )
    
    Without this apar fix, the plan will result in a TBSCAN operator
    against tab2 in order to apply the inlist predicate on column d.
    

Local fix

  • Applying the inlist 2 join transformation may help at optlevel
    7.
    

Problem summary

  • Users Affected : users with queries matching the description
    Problem summary : suboptimal performance for queries of this
    type
    Problem conclusion : this apar allows a better execution plan
    

Problem conclusion

  • This problem was first fixed in v9.5 fixpak 2
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI73109

  • Reported component name

    DB2 EDE LINUX

  • Reported component ID

    5724N7604

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-02-04

  • Closed date

    2008-09-01

  • Last modified date

    2008-09-01

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

    LI73096

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

Fix information

  • Fixed component name

    DB2 EDE LINUX

  • Fixed component ID

    5724N7604

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:
01 September 2008