Scenario: Improving cardinality estimates using statistical views

Consider the following query that computes annual sales revenue for golf clubs sold during July of each year:

   select  sum(f.sales_price), d2.year
     from  product d1, period d2, daily_sales f
     where  d1.prodkey = f.prodkey
       and  d2.perkey = f.perkey
       and  d1.item_desc = 'golf club'
       and  d2.month = 'JUL'
     group by  d2.year

Consider a database from a typical data warehouse, where STORE, CUSTOMER, PRODUCT, PROMOTION, and PERIOD are the dimension tables, and DAILY_SALES is the fact table. The following tables provide the definitions for these tables.

The table indicates that discounts for return customers represents only 2.86% of the 35 kinds of promotions that were offered.

The following query returns a count of 12 889 514:

   select  count(*)
     from  store d1, promotion d2, daily_sales f
     where  d1.storekey = f.storekey
       and  d2.promokey = f.promokey
       and  d1.store_number = '01'
       and  d2.promotype = 1

This query executes according to the following plan that is generated by the optimizer. In each node of this diagram, the first row is the cardinality estimate, the second row is the operator type, and the third row (the number in parentheses) is the operator ID.

                                          6.15567e+06
                                             IXAND
                                             (   8)
                             /------------------+------------------\
                      2.15448e+07                                2.15448e+08
                        NLJOIN                                     NLJOIN
                        (   9)                                     (  13)
                 /---------+--------\                       /---------+--------\
               1                  2.15448e+07            18                1.19694e+07
            FETCH                   IXSCAN             FETCH                   IXSCAN
            (  10)                  (  12)             (  14)                  (  16)
           /---+---\                  |               /---+---\                  |
         35         35           7.54069e+08        18         63          7.54069e+08
      IXSCAN  TABLE: DB2DBA  INDEX: DB2DBA       IXSCAN   TABLE: DB2DBA       INDEX: DB2DBA
      (  11)       PROMOTION     PROMO_FK_IDX     (  15)         STORE             STORE_FK_IDX
        |                                          |
        35                                         63
  INDEX: DB2DBA                                INDEX: DB2DBA
 PROMOTION_PK_IDX                              STOREX1

At the nested loop join (number 9), the optimizer estimates that around 2.86% of the product sold resulted from customers coming back to buy the same products at a discounted price (2.15448e+07 ÷ 7.54069e+08 ≈ 0.0286). Note that this is the same value before and after joining the PROMOTION table with the DAILY_SALES table. Table 8 summarizes the cardinality estimates and their percentage (the filtering effect) before and after the join.

What causes the optimizer to only estimate half of the actual number of records satisfying the two predicates? Store '01' represents about 28.57% of all the stores. What if other stores had more sales than store '01' (less than 28.57%)? Or what if store '01' actually sold most of the product (more than 28.57%)? Likewise, the 2.86% of products sold using promotion type 1 shown in Table 8 can be misleading. The actual percentage in DAILY_SALES could very well be a different figure than the projected one.

You can use statistical views to help the optimizer correct its estimates. First, you need to create two statistical views representing each semi-join in the previous query. The first statistical view provides the distribution of stores for all daily sales. The second statistical view represents the distribution of promotion types for all daily sales. Note that each statistical view can provide the distribution information for any particular store number or promotion type. In this example, you use a 10% sample rate to retrieve the records in DAILY_SALES for the respective views and save them in global temporary tables. You can then query those tables to collect the necessary statistics to update the two statistical views.

1. Create a view representing the join of STORE with DAILY_SALES.

      create view sv_store_dailysales as
        (select s.*
          from store s, daily_sales ds
          where s.storekey = ds.storekey)

2. Create a view representing the join of PROMOTION with DAILY_SALES.

      create view sv_promotion_dailysales as
        (select p.*
          from promotion.p, daily_sales ds
          where p.promokey = ds.promokey)

3. Make the views statistical views by enabling them for query optimization:

      alter view sv_store_dailysales enable query optimization
      alter view sv_promotion_dailysales enable query optimization

4. Execute the RUNSTATS command to collect statistics on the views:

      runstats on table db2dba.sv_store_dailysales with distribution
      runstats on table db2dba.sv_promotion_dailysales with distribution

5. Run the query again so that it can be re-optimized. Upon reoptimization, the optimizer will match SV_STORE_DAILYSALES and SV_PROMOTION_DAILYSALES with the query, and will use the view statistics to adjust the cardinality estimate of the semi-joins between the fact and dimension tables, causing a reversal of the original order of the semi-joins chosen without these statistics. The new plan is as follows:

                                             1.04627e+07
                                               IXAND
                                               (   8)
                               /------------------+------------------\
                        6.99152e+07                                1.12845e+08
                          NLJOIN                                     NLJOIN
                          (   9)                                     (  13)
                   /---------+--------\                       /---------+--------\
                18                 3.88418e+06             1                  1.12845e+08
              FETCH                   IXSCAN             FETCH                   IXSCAN
              (  10)                  (  12)             (  14)                  (  16)
             /---+---\                  |               /---+---\                  |
          18           63           7.54069e+08      35           35          7.54069e+08
        IXSCAN   TABLE:DB2DBA     INDEX: DB2DBA   IXSCAN    TABLE: DB2DBA    INDEX: DB2DBA DB2DBA
       (  11)      STORE            STORE_FK_IDX    (  15)       PROMOTION        PROMO_FK_IDX
          |                                          |
          63                                         35
      INDEX: DB2DBA                              INDEX: DB2DBA
      STOREX1                                    PROMOTION_PK_IDX

Table 9 summarizes the cardinality estimates and their percentage (the filtering effect) before and after the join for each semi-join.

Note that this time, the semi-join between STORE and DAILY_SALES is performed on the outer leg of the index ANDing plan. This is because the two statistical views essentially tell the optimizer that the predicate store_number = '01' will filter more rows than promotype = 1. This time, the optimizer estimates that there are approximately 10 462 700 products sold. This estimate is off by a factor of 1.23 (12 889 514 ÷ 10 462 700 ≈ 1.23), which is a significant improvement over the estimate without statistical views (in Table 8).