Creating Fact Materialized Views With OLAP Summary Advisor , 5 of 5

Sample Script for the COST Cube

The following sample script creates materialized views in concatenated rollup form for the COST_CUBE cube, which is mapped to the COSTS fact table in the SH schema.
This script creates two materialized views, one for each combination of hierarchies associated with the COST_CUBE cube.
create materialized view
COST_CUBE_1_OLAP
partition by range (gid) (
partition values less than(1),
partition values less than(3),
partition values less than(7),
partition values less than(16),
partition values less than(17),
partition values less than(19),
partition values less than(23),
partition values less than(48),
partition values less than(49),
partition values less than(51),
partition values less than(55),
partition values less than(112),
partition values less than(113),
partition values less than(115),
partition values less than(119),
partition values less than(MAXVALUE))
pctfree 5 pctused 40
tablespace SH_DATA
build immediate
using no index
refresh force
enable query rewrite
AS
SELECT
   GROUPING_ID(TIMES.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER_DESC,
   TIMES.CALENDAR_MONTH_DESC, TIMES.TIME_ID, PRODUCTS.PROD_TOTAL,
   PRODUCTS.PROD_CATEGORY, PRODUCTS.PROD_SUBCATEGORY,
   PRODUCTS.PROD_ID) gid,
   SUM(COSTS.UNIT_COST) SUM_OF_UNIT_COST,
   SUM(COSTS.UNIT_PRICE) SUM_OF_UNIT_PRICE,
   COUNT(*) COUNT_OF_STAR,
   TIMES.CALENDAR_YEAR CALENDAR_YEAR_1,
   TIMES.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC_2,
   TIMES.CALENDAR_MONTH_DESC CALENDAR_MONTH_DESC_3,
   TIMES.TIME_ID TIME_ID_4,
   PRODUCTS.PROD_TOTAL PROD_TOTAL_10,
   PRODUCTS.PROD_CATEGORY PROD_CATEGORY_11,
   PRODUCTS.PROD_SUBCATEGORY PROD_SUBCATEGORY_12,
   PRODUCTS.PROD_ID PROD_ID_13
FROM
   SH.TIMES TIMES, 
   SH.PRODUCTS PRODUCTS, 
   SH.COSTS COSTS 
WHERE
   (TIMES.TIME_ID = COSTS.TIME_ID) AND
   (PRODUCTS.PROD_ID = COSTS.PROD_ID) 
GROUP BY 
   TIMES.CALENDAR_YEAR , 
   ROLLUP
      (TIMES.CALENDAR_QUARTER_DESC, TIMES.CALENDAR_MONTH_DESC, TIMES.TIME_ID),
   PRODUCTS.PROD_TOTAL , 
   ROLLUP
      (PRODUCTS.PROD_CATEGORY, PRODUCTS.PROD_SUBCATEGORY, PRODUCTS.PROD_ID);

execute dbms_stats.gather_table_stats('SH', 'COST_CUBE_1_OLAP',  degree=>
   dbms_stats.default_degree, estimate_percent=>
   dbms_stats.auto_sample_size, method_opt=>
   'for all columns size 1 for columns size 254 GID' , granularity=>'GLOBAL') ;
ALTER TABLE COST_CUBE_1_OLAP MINIMIZE RECORDS_PER_BLOCK ; 

CREATE BITMAP INDEX BI_COST_CALENAR_QUESC_2_1 ON COST_CUBE_1_OLAP(CALENDAR_
QUARTER_DESC_2)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_CALENAR_MOESC_3_1 ON COST_CUBE_1_OLAP(CALENDAR_
MONTH_DESC_3)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_TIME_D_ID_4_1 ON COST_CUBE_1_OLAP(TIME_ID_4)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_PROD_ATEGOORY_22_1 ON COST_CUBE_1_OLAP(PROD_
CATEGORY_11)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_PROD_UBCATORY_24_1 ON COST_CUBE_1_OLAP(PROD_
SUBCATEGORY_12)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_PROD_D_ID_26_1 ON COST_CUBE_1_OLAP(PROD_ID_13)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

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

execute cwm2_olap_cube.set_mv_summary_code('SH', 'COST_CUBE', 'ROLLUP') ;

create materialized view
COST_CUBE_2_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(32),
partition values less than(33),
partition values less than(35),
partition values less than(39),
partition values less than(47),
partition values less than(96),
partition values less than(97),
partition values less than(99),
partition values less than(103),
partition values less than(111),
partition values less than(224),
partition values less than(225),
partition values less than(227),
partition values less than(231),
partition values less than(239),
partition values less than(MAXVALUE))
pctfree 5 pctused 40
tablespace SH_DATA
build immediate
using no index
refresh force
enable query rewrite
AS
SELECT
   GROUPING_ID(PRODUCTS.PROD_TOTAL, PRODUCTS.PROD_CATEGORY,
   PRODUCTS.PROD_SUBCATEGORY, PRODUCTS.PROD_ID, TIMES.FISCAL_YEAR,
   TIMES.FISCAL_QUARTER_DESC, TIMES.FISCAL_MONTH_DESC,
   TIMES.WEEK_ENDING_DAY, TIMES.TIME_ID) gid,
   SUM(COSTS.UNIT_COST) SUM_OF_UNIT_COST,
   SUM(COSTS.UNIT_PRICE) SUM_OF_UNIT_PRICE,
   COUNT(*) COUNT_OF_STAR,
   TIMES.FISCAL_YEAR FISCAL_YEAR_5,
   TIMES.FISCAL_QUARTER_DESC FISCAL_QUARTER_DESC_6,
   TIMES.FISCAL_MONTH_DESC FISCAL_MONTH_DESC_7,
   TIMES.WEEK_ENDING_DAY WEEK_ENDING_DAY_8,
   TIMES.TIME_ID TIME_ID_9,
   PRODUCTS.PROD_TOTAL PROD_TOTAL_10,
   PRODUCTS.PROD_CATEGORY PROD_CATEGORY_11,
   PRODUCTS.PROD_SUBCATEGORY PROD_SUBCATEGORY_12,
   PRODUCTS.PROD_ID PROD_ID_13
FROM
   SH.PRODUCTS PRODUCTS, 
   SH.TIMES TIMES, 
   SH.COSTS COSTS 
WHERE
   (PRODUCTS.PROD_ID = COSTS.PROD_ID) AND
   (TIMES.TIME_ID = COSTS.TIME_ID) GROUP BY 
   PRODUCTS.PROD_TOTAL , 
     ROLLUP
      (PRODUCTS.PROD_CATEGORY, PRODUCTS.PROD_SUBCATEGORY, PRODUCTS.PROD_ID),
   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', 'COST_CUBE_2_OLAP',  degree=>
   dbms_stats.default_degree, estimate_percent=>
   dbms_stats.auto_sample_size, method_opt=>
   'for all columns size 1 for columns size 254 GID' , granularity=>'GLOBAL') ;
ALTER TABLE COST_CUBE_2_OLAP MINIMIZE RECORDS_PER_BLOCK ; 

CREATE BITMAP INDEX BI_COST_PROD_ATEGOORY_33_2 ON COST_CUBE_2_OLAP(PROD_
CATEGORY_11)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_PROD_UBCATORY_36_2 ON COST_CUBE_2_OLAP(PROD_
SUBCATEGORY_12)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_PROD_D_ID_39_2 ON COST_CUBE_2_OLAP(PROD_ID_13)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_FISCA_QUARESC_24_2 ON COST_CUBE_2_OLAP(FISCAL_
QUARTER_DESC_6)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;


CREATE BITMAP INDEX BI_COST_FISCA_MONTESC_28_2 ON COST_CUBE_2_OLAP(FISCAL_MONTH_
DESC_7)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_WEEK_NDINGDAY_32_2 ON COST_CUBE_2_OLAP(WEEK_ENDING_
DAY_8)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BI_COST_TIME_D_ID_36_2 ON COST_CUBE_2_OLAP(TIME_ID_9)
LOCAL
COMPUTE STATISTICS
TABLESPACE SH_IDX
PARALLEL PCTFREE 0
NOLOGGING;

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

execute cwm2_olap_cube.set_mv_summary_code('SH', 'COST_CUBE', 'ROLLUP') ;