IBM Support

IZ05878: PERFORMANCE PROBLEM FOR QUERIES CONTAINING OUTER JOIN WITH A VERY LARGE INNER TABLE WITH NON COMPATIBLE JOIN PREDICATES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Performance problems may occur for queries containing an OUTER
    JOIN with a very large inner table and when the columns from the
    inner table involved in the join predicates are not compatible
    with the table partitioning columns of the inner table. A
    db2exfmt of the query will show that the inner table feeds a DTQ
    prior to the application of the join.
    
    The following example of a table partitioning and join columns
    illustrates the problem.
    
    A very large table partitioned accross multiple database
    partitions
    
    CREATE TABLE GEOGRAPHY  (
              GEOGRAPHYKEY INTEGER NOT NULL ,
              RLSE_STEP_PN CHAR(12) NOT NULL ,
              RLSE_STEP_EC CHAR(12) NOT NULL ,
              NORMALIZED_TESTX SMALLINT NOT NULL ,
              NORMALIZED_TESTY SMALLINT NOT NULL ,
              KERF_X SMALLINT ,
              KERF_Y SMALLINT  )
             DISTRIBUTE BY HASH(GEOGRAPHYKEY)
    in multinodets;
    
    A much smaller table, possible distributed accross multiple
    database partitiong or in a single node tablespace:
    
    CREATE TABLE GEOGRAPHYSTAT  (
              GEOGRAPHYKEY INTEGER NOT NULL ,
              PRODUCTIONKEY INTEGER NOT NULL WITH DEFAULT  ,
              RLSE_STEP_PN CHAR(12) NOT NULL WITH DEFAULT  ,
              RLSE_STEP_EC CHAR(12) NOT NULL WITH DEFAULT  ,
              NORMALIZED_TESTX SMALLINT NOT NULL ,
              NORMALIZED_TESTY SMALLINT NOT NULL ,
              GEOGRAPHY_ORIG_SYS CHAR(12) NOT NULL WITH DEFAULT  ,
              GEOGRAPHY_UPDT_TS TIMESTAMP NOT NULL WITH DEFAULT  ,
              GEOGRAPHY_RC_TS TIMESTAMP NOT NULL WITH DEFAULT  ,
              STATUS CHAR(1) NOT NULL WITH DEFAULT  ,
              FAILCODE INTEGER NOT NULL WITH DEFAULT  )
    IN singlenodets;
    
    The following query may experience poor performance if the
    GEOGRAPHY table is much larger then the GEOGRAPHYSTAT table.
    
    SELECT  G_T0.GEOGRAPHYKEY ,
            G_T0.STATUS ,
            G_T0.GEOGRAPHY_UPDT_TS ,
            G_T1.GEOGRAPHYKEY,
            G_T1.RLSE_STEP_PN ,
            G_T1.RLSE_STEP_EC ,
            G_T1.NORMALIZED_TESTX ,
            G_T1.NORMALIZED_TESTY ,
            G_T1.KERF_X ,
            G_T1.KERF_Y
    FROM            GEOGRAPHYSTAT AS G_T0
    LEFT OUTER JOIN GEOGRAPHY     AS G_T1
    ON      (G_T1.RLSE_STEP_PN     = G_T0.RLSE_STEP_PN)
        AND (G_T1.RLSE_STEP_EC     = G_T0.RLSE_STEP_EC)
        AND (G_T1.NORMALIZED_TESTX = G_T0.NORMALIZED_TESTX)
        AND (G_T1.NORMALIZED_TESTY = G_T0.NORMALIZED_TESTY);
    
    Note that GEOGRAPHY is partitioned on column GEOGRAPHYKEY which
    is not a column that is used in the ON clause predicates of the
    OUTER JOIN.
    

Local fix

  • No easy workarounds. Repartitioning the table or manual
    rewriting the query to pre-join the GEOGRAPHYSTAT table to the
    GEOGRAPHY table may result in a better plan.
    

Problem summary

  • See Problem Description.
    

Problem conclusion

  • First fixed in DB2 Version 9.1, FixPak 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IZ05878

  • Reported component name

    DB2 CEE AIX

  • Reported component ID

    5765F3000

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2007-10-03

  • Closed date

    2008-07-14

  • Last modified date

    2008-07-14

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

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

    IZ24282

Fix information

  • Fixed component name

    DB2 CEE AIX

  • Fixed component ID

    5765F3000

Applicable component levels

  • R910 PSY

       UP

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

Document Information

Modified date:
01 October 2021