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. | |
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 |