Avoiding redundant predicates

Avoid redundant predicates, especially when they occur across different tables. In some cases, the optimizer cannot detect that the predicates are redundant. This might result in cardinality underestimation.

For example, within SAP business intelligence (BI) applications, the snowflake schema with fact and dimension tables is used as a query optimized data structure. In some cases, there is a redundant time characteristic column ("SID_0CALMONTH" for month or "SID_0FISCPER" for year) defined on the fact and dimension tables.

The SAP BI online analytical processing (OLAP) processor generates redundant predicates on the time characteristics column of the dimension and fact tables.

These redundant predicates might result in longer query run time.

The following section provides an example with two redundant predicates that are defined in the WHERE condition of a SAP BI query. Identical predicates are defined on the time dimension (DT) and fact (F) table:

     AND (     "DT"."SID_0CALMONTH" = 199605
           AND "F". "SID_0CALMONTH" = 199605
            OR "DT"."SID_0CALMONTH" = 199705
           AND "F". "SID_0CALMONTH" = 199705 )
 AND NOT (     "DT"."SID_0CALMONTH" = 199803
           AND "F". "SID_0CALMONTH" = 199803 )

The Db2® optimizer does not recognize the predicates as identical, and treats them as independent. This leads to underestimation of cardinalities, suboptimal query access plans, and longer query run times.

For that reason, the redundant predicates are removed by the Db2 database platform-specific software layer.

These predicates are transferred to the following ones and only the predicates on the fact table column "SID_0CALMONTH" remain:

    AND (     "F". "SID_0CALMONTH" = 199605
           OR "F". "SID_0CALMONTH" = 199705 )
AND NOT (     "F". "SID_0CALMONTH" = 199803 )

Apply the instructions in SAP notes 957070 and 1144883 to remove the redundant predicates.