Use Materialized Views

As for aggregates, you should always implement them as materialized views, period. First, no matter what business intelligence tool your end-users select, query rewrites can be accomplished and are desirable. Second, regardless of which aggregation method you implement from the previous section, they all will work equally well against a materialized view (since it's nothing more than a locally replicated table). There are no downsides to implementing aggregates as materialized views, therefore you should always do so. And for those of you who already have a data warehouse built, go back and create materialized views on your pre-existing aggregate tables. This way, you too can get query rewrites even though you're not using any other materialized view features.

Here are the basic implementation guidelines:

1. Create Oracle dimensions for each dimension table.

2. Enable dimension primary key constraints with NOVALIDATE (if they don't exist).

3. Enable fact primary key constraint with NOVALIDATE (using existing unique index).

4. Enable fact to dimension foreign key constraints with NOVALIDATE .

5. Create materialized view logs on dimensions.

6. Create a materialized view log on the base fact table.

7. Create materialized views with query rewrite enabled for aggregates.

8. Create star transformation bitmap indexes and statistics on materialized views.

9. Use Oracle Enterprise Manager's Summary Advisor to gauge effectiveness.

Detailed below are the above steps applied to this book's simple data warehousing data model shown in Figure 7-6:

1. Create Oracle dimensions for each dimension table:

    CREATE DIMENSION time_dim   LEVEL curdate    IS period.period_date   LEVEL month      IS period.period_month   LEVEL quarter    IS period.period_quarter   LEVEL year       IS period.period_year   LEVEL week_num   IS period.week_number HIERARCHY calendar_rollup(   curdate       CHILD OF   month         CHILD OF   quarter       CHILD OF   year) HIERARCHY weekly_rollup(   curdate          CHILD OF   week_num) ATTRIBUTE curdate DETERMINES period.day_of_wk; 

2. Enable dimension primary key constraints with NOVALIDATE (if they don't exist):

    alter table period     add constraint period_pk     primary key (period_id)     novalidate; alter table location     add constraint location_pk     primary key (location_id)     novalidate; alter table product     add constraint product_pk     primary key (product_id)     novalidate; 

3. Enable fact primary key constraint with NOVALIDATE (using existing unique index):

    alter table pos_day     add constraint pos_day_pk     primary key (PERIOD_ID, LOCATION_ID, PRODUCT_ID)     using index pos_day_pk     novalidate; 

4. Enable fact to dimension foreign key constraints with NOVALIDATE :

    alter table pos_day     add constraint pos_day_fk1       foreign key (period_id) references period(period_id)     novalidate; alter table pos_day     add constraint pos_day_fk2     foreign key (location_id) references location(location_id)     novalidate; alter table pos_day     add constraint pos_day_fk3     foreign key (product_id) references product(product_id)     novalidate; 

5. Create materialized view logs on dimensions:

    create materialized view log on period   WITH SEQUENCE, ROWID (  PERIOD_ID,  PERIOD_NAME,  LEVELX,  CURRENT_FLAG,  PERIOD_DATE,  PERIOD_WEEK,  PERIOD_MONTH,  PERIOD_QUARTER,  PERIOD_YEAR,  DAY_NUMBER_OF_WK,  DAY_NUMBER_OF_MTH,  HOLIDAY_FLAG,  WEEKEND_FLAG,  WORKDAY_FLAG,  DAY_OF_WK,  WEEK_NUMBER ) INCLUDING NEW VALUES; 

6. Create a materialized view log on the base fact table:

    create materialized view log on pos_day   WITH SEQUENCE, ROWID (  PERIOD_ID,  LOCATION_ID,  PRODUCT_ID,  SALES_UNIT,  SALES_RETAIL,  GROSS_PROFIT ) INCLUDING NEW VALUES; 

7. Create materialized views with query rewrite enabled for aggregates:

    create materialized view mv_pos_week parallel (degree 1) nologging BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE as select  /*+ parallel(pos_day,1) full(pos_day) */   period.wk_id period_id, location_id, product_id,   sum(nvl(sales_unit,0)),   sum(nvl(sales_retail,0)),   sum(nvl(gross_profit,0)) from pos_day,       (select a.period_id wk_id, b.period_id d1_id, c.period_id d2_id       from period a,            period b,            period c       where a.levelx='WEEK'         and b.levelx='DAY'         and c.levelx='DAY'         and a.period_date     = b.period_date         and a.period_date + 6 = c.period_date         and exists (select 1                     from pos_day                         where period_id between b.period_id and c.period_id                    )      ) period where period_id between period.d1_id and period.d2_id group by period.wk_id, location_id, product_id; 

8. Create star transformation bitmap indexes and statistics on materialized views:

    CREATE BITMAP INDEX MV_POS_WEEK_B1 ON MV_POS_WEEK (PERIOD_ID)        PCTFREE 1        NOLOGGING; CREATE BITMAP INDEX MV_POS_WEEK_B2 ON MV_POS_WEEK (LOCATION_ID)        PCTFREE 1        NOLOGGING; CREATE BITMAP INDEX MV_POS_WEEK_B3 ON MV_POS_WEEK (PRODUCT_ID)        PCTFREE 1        NOLOGGING; analyze table mv_pos_week   estimate statistics   for table   for all indexes   for all indexed columns sample 20000 rows; 

9. Use Oracle Enterprise Manager's Summary Advisor to gauge effectiveness (shown in Figure 7-7)

   Figure 7-7. Summary Management via Oracle Enterprise Manager