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:

Create Oracle dimensions for each dimension table.
Enable dimension primary key constraints with NOVALIDATE (if they don't exist).
Enable fact primary key constraint with NOVALIDATE (using existing unique index).
Enable fact to dimension foreign key constraints with NOVALIDATE.
Create materialized view logs on dimensions.
Create a materialized view log on the base fact table.
Create materialized views with query rewrite enabled for aggregates.
Create star transformation bitmap indexes and statistics on materialized views.
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:

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;

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;

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;

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;

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;

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;

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;

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;

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

Figure 7-7. Summary Management via Oracle Enterprise Manager