Creating Dimension Materialized Views , 4 of 5

Sample Script for the TIMES_DIM Dimension

The following sample script creates materialized views for the TIMES_DIM dimension in the SH schema. This script could result from running OLAP Summary Advisor or from invoking the DBMS_ODM.CREATEDIMMV_GS procedure.
The script creates two materialized views: one for the CAL_ROLLUP hierarchy, and one for the FIS_ROLLUP hierarchy
CREATE materialized view  TIMES_CAL_R_OLAP
partition by range (gid) (
partition values less than(1),
partition values less than(3),
partition values less than(7),
partition values less than(MAXVALUE))
TABLESPACE SH_DATABUILD IMMEDIATE
USING NO INDEX
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT
   COUNT(*) COUNT_STAR,
   GROUPING_ID( TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER_DESC,
                TIMES.CALENDAR_MONTH_DESC,  TIMES.TIME_ID) gid,
   max(TIMES.CALENDAR_YEAR) CALENDAR_YEAR_AR,
   max(TIMES.END_OF_CAL_YEAR) END_OF_CAL_YEAR_AR,
   max(TIMES.DAYS_IN_CAL_YEAR) DAYS_IN_CAL_YEAR_AR,
   max(TIMES.CALENDAR_QUARTER_DESC) CALENDAR_QUARTER_DESC_AR,
   max(TIMES.END_OF_CAL_QUARTER) END_OF_CAL_QUARTER_AR,
   max(TIMES.DAYS_IN_CAL_QUARTER) DAYS_IN_CAL_QUARTER_AR,
   max(TIMES.CALENDAR_QUARTER_NUMBER) CALENDAR_QUARTER_NUMBER_AR,
   max(TIMES.CALENDAR_MONTH_DESC) CALENDAR_MONTH_DESC_AR,
   max(TIMES.END_OF_CAL_MONTH) END_OF_CAL_MONTH_AR,
   max(TIMES.DAYS_IN_CAL_MONTH) DAYS_IN_CAL_MONTH_AR,
   max(TIMES.CALENDAR_MONTH_NAME) CALENDAR_MONTH_NAME_AR,
   max(TIMES.CALENDAR_MONTH_NUMBER) CALENDAR_MONTH_NUMBER_AR,
   max(TIMES.DAY_NUMBER_IN_WEEK) DAY_NUMBER_IN_WEEK_AR,
   max(TIMES.CALENDAR_WEEK_NUMBER) CALENDAR_WEEK_NUMBER_AR,
   max(TIMES.DAY_NUMBER_IN_MONTH) DAY_NUMBER_IN_MONTH_AR,
   max(TIMES.DAY_NAME) DAY_NAME_AR,
   TIMES.CALENDAR_YEAR CALENDAR_YEAR, 
   TIMES.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC, 
   TIMES.CALENDAR_MONTH_DESC CALENDAR_MONTH_DESC, 
   TIMES.TIME_ID TIME_ID
FROM 
   SH.TIMES  TIMES  
GROUP BY 
   TIMES.CALENDAR_YEAR , 
   ROLLUP(TIMES.CALENDAR_QUARTER_DESC,TIMES.CALENDAR_MONTH_DESC,TIMES.TIME_ID):

execute dbms_stats.gather_table_stats ('SH', 'TIMES_CAL_R_OLAP', degree=>
   dbms_stats.default_degree,method_opt=>'for all columns size skewonly') ;
ALTER TABLE TIMES_CAL_R_OLAP MINIMIZE RECORDS_PER_BLOCK ; 

CREATE BITMAP INDEX MV_CALENDAR_QUARTER_DESCCA_BI2 ON TIMES_CAL_R_OLAP
   (CALENDAR_QUARTER_DESC)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_CALENDAR_MONTH_DESCCA_BI3 ON TIMES_CAL_R_OLAP
   (CALENDAR_MONTH_DESC)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_TIME_IDCA_BI4 ON TIMES_CAL_R_OLAP
   (TIME_ID)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_GID_CA_BI_4 ON TIMES_CAL_R_OLAP
   (gid)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_TIMES_CAL_R_OLAP_PREL_FI ON TIMES_CAL_R_OLAP
   ( (CASE GID
          WHEN(7) THEN NULL
          WHEN(3) THEN TO_CHAR( CALENDAR_YEAR)
          WHEN(1) THEN TO_CHAR( CALENDAR_QUARTER_DESC)
          ELSE TO_CHAR( CALENDAR_MONTH_DESC) END) )
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_TIMES_CAL_R_OLAP_ET_FI ON TIMES_CAL_R_OLAP
   ( (CASE GID
          WHEN(7) THEN TO_CHAR( CALENDAR_YEAR)
          WHEN(3) THEN TO_CHAR( CALENDAR_QUARTER_DESC)
          WHEN(1) THEN TO_CHAR( CALENDAR_MONTH_DESC)
          ELSE TO_CHAR( TIME_ID) END) )
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

execute dbms_stats.gather_table_stats('SH', 'TIMES_CAL_R_OLAP',  
   degree=>dbms_stats.default_degree, estimate_percent=>
   dbms_stats.auto_sample_size, method_opt=>'for all hidden columns size 254') ;

create materialized view  TIMES_FIS_R_OLAP
partition by range (gid) (
partition values less than(1),
partition values less than(3),
partition values less than(7),
partition values less than(15),
partition values less than(MAXVALUE))
TABLESPACE SH_DATA
BUILD IMMEDIATE
USING NO INDEX
REFRESH FORCE
ENABLE QUERY REWRITE
AS
SELECT
   COUNT(*) COUNT_STAR,
   GROUPING_ID( TIMES.FISCAL_YEAR,
   TIMES.FISCAL_QUARTER_DESC,
   TIMES.FISCAL_MONTH_DESC,
   TIMES.WEEK_ENDING_DAY,
   TIMES.TIME_ID) gid,
   max(TIMES.FISCAL_YEAR) FISCAL_YEAR_AR,
   max(TIMES.END_OF_FIS_YEAR) END_OF_FIS_YEAR_AR,
   max(TIMES.DAYS_IN_FIS_YEAR) DAYS_IN_FIS_YEAR_AR,
   max(TIMES.FISCAL_QUARTER_DESC) FISCAL_QUARTER_DESC_AR,
   max(TIMES.END_OF_FIS_QUARTER) END_OF_FIS_QUARTER_AR,
   max(TIMES.DAYS_IN_FIS_QUARTER) DAYS_IN_FIS_QUARTER_AR,
   max(TIMES.FISCAL_QUARTER_NUMBER) FISCAL_QUARTER_NUMBER_AR,
   max(TIMES.FISCAL_MONTH_DESC) FISCAL_MONTH_DESC_AR,
   max(TIMES.END_OF_FIS_MONTH) END_OF_FIS_MONTH_AR,
   max(TIMES.DAYS_IN_FIS_MONTH) DAYS_IN_FIS_MONTH_AR,
   max(TIMES.FISCAL_MONTH_NAME) FISCAL_MONTH_NAME_AR,
   max(TIMES.FISCAL_MONTH_NUMBER) FISCAL_MONTH_NUMBER_AR,
   max(TIMES.WEEK_ENDING_DAY) WEEK_ENDING_DAY_AR,
   max(TIMES.FISCAL_WEEK_NUMBER) FISCAL_WEEK_NUMBER_AR,
   max(TIMES.DAY_NUMBER_IN_WEEK) DAY_NUMBER_IN_WEEK_AR,
   max(TIMES.CALENDAR_WEEK_NUMBER) CALENDAR_WEEK_NUMBER_AR,
   max(TIMES.DAY_NUMBER_IN_MONTH) DAY_NUMBER_IN_MONTH_AR,
   max(TIMES.DAY_NAME) DAY_NAME_AR,
   TIMES.FISCAL_YEAR FISCAL_YEAR, 
   TIMES.FISCAL_QUARTER_DESC FISCAL_QUARTER_DESC, 
   TIMES.FISCAL_MONTH_DESC FISCAL_MONTH_DESC, 
   TIMES.WEEK_ENDING_DAY WEEK_ENDING_DAY, 
   TIMES.TIME_ID TIME_ID
FROM 
   SH.TIMES  TIMES  
GROUP BY 
   TIMES.FISCAL_YEAR , ROLLUP( TIMES.FISCAL_QUARTER_DESC , 
      TIMES.FISCAL_MONTH_DESC , TIMES.WEEK_ENDING_DAY , TIMES.TIME_ID );

execute dbms_stats.gather_table_stats('SH', 'TIMES_FIS_R_OLAP',  
   degree=>dbms_stats.default_degree,method_opt=>
   'for all columns size skewonly') ;
ALTER TABLE TIMES_FIS_R_OLAP MINIMIZE RECORDS_PER_BLOCK ; 

CREATE BITMAP INDEX MV_FISCAL_QUARTER_DESCFI_BI8 ON TIMES_FIS_R_OLAP
   (FISCAL_QUARTER_DESC)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_FISCAL_MONTH_DESCFI_BI12 ON TIMES_FIS_R_OLAP
   (FISCAL_MONTH_DESC)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_WEEK_ENDING_DAYFI_BI16 ON TIMES_FIS_R_OLAP
   (WEEK_ENDING_DAY)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_TIME_IDFI_BI20 ON TIMES_FIS_R_OLAP
   (TIME_ID)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_GID_FI_BI_20 ON TIMES_FIS_R_OLAP
   (gid)
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_TIMES_FIS_R_OLAP_PREL_FI ON TIMES_FIS_R_OLAP
( (CASE GID
          WHEN(15) THEN NULL
          WHEN(7) THEN TO_CHAR( FISCAL_YEAR)
          WHEN(3) THEN TO_CHAR( FISCAL_QUARTER_DESC)
          WHEN(1) THEN TO_CHAR( FISCAL_MONTH_DESC)
          ELSE TO_CHAR( WEEK_ENDING_DAY) END) )
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

CREATE BITMAP INDEX MV_TIMES_FIS_R_OLAP_ET_FI ON TIMES_FIS_R_OLAP
( (CASE GID
          WHEN(15) THEN TO_CHAR( FISCAL_YEAR)
          WHEN(7) THEN TO_CHAR( FISCAL_QUARTER_DESC)
          WHEN(3) THEN TO_CHAR( FISCAL_MONTH_DESC)
          WHEN(1) THEN TO_CHAR( WEEK_ENDING_DAY)
          ELSE TO_CHAR( TIME_ID) END) )
TABLESPACE SH_IDX
PCTFREE 0
COMPUTE STATISTICS
LOCAL
NOLOGGING;

execute dbms_stats.gather_table_stats('SH', 'TIMES_FIS_R_OLAP',  
   degree=>dbms_stats.default_degree, estimate_percent=>
   dbms_stats.auto_sample_size, method_opt=>'for all hidden columns size 254') ;