IBM Support

LI74409: INACCURATE CARDINALITY ESTIMATES FROM MULTIPLE ELIGIBLE STATISTICAL VIEWS THAT HAVE DIFFERENT NUMBER OF PREDICATES

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Description:
    When the statistics of multiple statistical views are used by
    the optimizer, some cardinality estimates may be inaccurrate, as
    in the following example where the statistics of two statisical
    views
    
    -- generic statistical view
    --
    CREATE VIEW FACT_DIM_SV AS
    SELECT f.*, d.DIM_KEY d
    FROM  FACT f, DIM d
    WHERE f.DIM_KEY = d.DIM_KEY;
    
    -- specific statistical view for queries with d.code=10 local
    predicate
    --
    CREATE VIEW FACT_DIM_SV2 AS
    SELECT f.*, d.DIM_KEY d
    FROM  FACT f, DIM d
    WHERE f.DIM_KEY = d.DIM_KEY
    AND d.code = 10;
    
    are used by the optimizer when compiling a query such as:
    
    SELECT SUM(f.amount), d.code
    FROM FACT f, DIM d
    WHERE f.DIM_KEY = d.DIM_KEY
    AND d.code = 10
    AND d.era = 1;
    

Local fix

  • Cardinality estimates may improve if the generic statistical
    view is dropped.  However, queries that do not contain the
    predicates of the remaining specific statistic view will not
    benefit from its statistics.
    

Problem summary

  • When the statistics of multiple statistical views are used by
    
    the optimizer, some cardinality estimates may be inaccurrate, as
    in the following example where the statistics of two statisical
    views
    
    -- generic statistical view
    --
    CREATE VIEW FACT_DIM_SV AS
    SELECT f.*, d.DIM_KEY d
    FROM  FACT f, DIM d
    WHERE f.DIM_KEY = d.DIM_KEY;
    
    -- specific statistical view for queries with d.code=10 local
    predicate
    --
    CREATE VIEW FACT_DIM_SV2 AS
    SELECT f.*, d.DIM_KEY d
    FROM  FACT f, DIM d
    WHERE f.DIM_KEY = d.DIM_KEY
    AND d.code = 10;
    
    are used by the optimizer when compiling a query such as:
    
    SELECT SUM(f.amount), d.code
    FROM FACT f, DIM d
    WHERE f.DIM_KEY = d.DIM_KEY
    AND d.code = 10
    AND d.era = 1;
    

Problem conclusion

  • This APAR is first fixed in DB2 V9.5 FixPack5
    

Temporary fix

  • Cardinality estimates may improve if the generic statistical
    view is dropped.  However, queries that do not contain the
    predicates of the remaining specific statistic view will not
    benefit from its statistics.
    

Comments

APAR Information

  • APAR number

    LI74409

  • 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-04-09

  • Closed date

    2010-01-15

  • Last modified date

    2010-01-15

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

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

    LI74410 LI74413 IC62285

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:
15 January 2010