Sample Script for the COST Cube

The following sample script, generated by DBMS_ODM.CREATEFACTMV_GS, creates a materialized view in grouping set form for the COST_CUBE cube, which is mapped to the COSTS fact table in the SH schema.

This script contains all level combinations for all hierarchies. To deselect levels and level combinations, edit the tables generated by the CREATEDIMLEVTUPLE Procedure and the CREATECUBELEVELTUPLE Procedure before invoking CREATEFACTMV_GS Procedure.

create materialized view
COST_CUBE_2_OLAP
partition by range (gid) (
partition values less than(1),
partition values less than(62),
partition values less than(126),
partition values less than(254),
partition values less than(450),
partition values less than(454),
partition values less than(462),
partition values less than(478),
partition values less than(512),
partition values less than(574),
partition values less than(638),
partition values less than(766),
partition values less than(962),
partition values less than(966),
partition values less than(974),
partition values less than(990),
partition values less than(1536),
partition values less than(1598),
partition values less than(1662),
partition values less than(1790),
partition values less than(1986),
partition values less than(1990),
partition values less than(1998),
partition values less than(2014),
partition values less than(3584),
partition values less than(3646),
partition values less than(3710),
partition values less than(3838),
partition values less than(4034),
partition values less than(4038),
partition values less than(4046),
partition values less than(4062),
partition values less than(MAXVALUE))
pctfree 5 pctused 40
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.CALENDAR_YEAR, TIMES.CALENDAR_QUARTER_DESC,
   TIMES.CALENDAR_MONTH_DESC, 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,
   PRODUCTS.PROD_TOTAL PROD_TOTAL_77,
   PRODUCTS.PROD_CATEGORY PROD_CATEGORY_78,
   PRODUCTS.PROD_SUBCATEGORY PROD_SUBCATEGORY_79,
   PRODUCTS.PROD_ID PROD_ID_80,
   TIMES.CALENDAR_YEAR CALENDAR_YEAR_169,
   TIMES.CALENDAR_QUARTER_DESC CALENDAR_QUARTER_DESC_170,
   TIMES.CALENDAR_MONTH_DESC CALENDAR_MONTH_DESC_171,
   TIMES.FISCAL_YEAR FISCAL_YEAR_172,
   TIMES.FISCAL_QUARTER_DESC FISCAL_QUARTER_DESC_173,
   TIMES.FISCAL_MONTH_DESC FISCAL_MONTH_DESC_174,
   TIMES.WEEK_ENDING_DAY WEEK_ENDING_DAY_175,
   TIMES.TIME_ID TIME_ID_176
FROM
   SH.PRODUCTS PRODUCTS, 
   SH.TIMES TIMES, 
   SH.COSTS COSTS 
WHERE
   (TIMES.TIME_ID = COSTS.TIME_ID) AND
   (PRODUCTS.PROD_ID = COSTS.PROD_ID) 
    GROUP BY GROUPING SETS 

      ( (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
         PRODUCTS.PROD_SUBCATEGORY , PRODUCTS.PROD_ID , TIMES.CALENDAR_YEAR ,
         TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC , 
         TIMES.FISCAL_YEAR , TIMES.FISCAL_QUARTER_DESC ,
         TIMES.FISCAL_MONTH_DESC , TIMES.WEEK_ENDING_DAY , TIMES.TIME_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 ), 

       (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
        PRODUCTS.PROD_SUBCATEGORY , PRODUCTS.PROD_ID , TIMES.FISCAL_YEAR ,
        TIMES.FISCAL_QUARTER_DESC , TIMES.FISCAL_MONTH_DESC ), 

       (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
        PRODUCTS.PROD_SUBCATEGORY , PRODUCTS.PROD_ID , TIMES.CALENDAR_YEAR ,
        TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC ), 

       (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
        PRODUCTS.PROD_SUBCATEGORY , PRODUCTS.PROD_ID , TIMES.FISCAL_YEAR ,
        TIMES.FISCAL_QUARTER_DESC ), 

       (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
        PRODUCTS.PROD_SUBCATEGORY , PRODUCTS.PROD_ID , TIMES.CALENDAR_YEAR ,
        TIMES.CALENDAR_QUARTER_DESC ), 

        (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
         PRODUCTS.PROD_SUBCATEGORY , PRODUCTS.PROD_ID , TIMES.FISCAL_YEAR ), 

        (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
         PRODUCTS.PROD_SUBCATEGORY , PRODUCTS.PROD_ID , TIMES.CALENDAR_YEAR ), 
 
        (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
         PRODUCTS.PROD_SUBCATEGORY , TIMES.CALENDAR_YEAR ,
         TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC , 
         TIMES.FISCAL_YEAR , TIMES.FISCAL_QUARTER_DESC ,
         TIMES.FISCAL_MONTH_DESC , TIMES.WEEK_ENDING_DAY , TIMES.TIME_ID ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
          PRODUCTS.PROD_SUBCATEGORY , TIMES.FISCAL_YEAR , 
          TIMES.FISCAL_QUARTER_DESC , TIMES.FISCAL_MONTH_DESC , 
          TIMES.WEEK_ENDING_DAY ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
          PRODUCTS.PROD_SUBCATEGORY , TIMES.FISCAL_YEAR , 
          TIMES.FISCAL_QUARTER_DESC , TIMES.FISCAL_MONTH_DESC ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
          PRODUCTS.PROD_SUBCATEGORY , TIMES.CALENDAR_YEAR ,
          TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
          PRODUCTS.PROD_SUBCATEGORY , TIMES.FISCAL_YEAR , 
          TIMES.FISCAL_QUARTER_DESC ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
          PRODUCTS.PROD_SUBCATEGORY , TIMES.CALENDAR_YEAR ,
          TIMES.CALENDAR_QUARTER_DESC ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
          PRODUCTS.PROD_SUBCATEGORY , TIMES.FISCAL_YEAR ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , 
          PRODUCTS.PROD_SUBCATEGORY , TIMES.CALENDAR_YEAR ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.CALENDAR_YEAR ,
          TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC ,
          TIMES.FISCAL_YEAR , TIMES.FISCAL_QUARTER_DESC , 
          TIMES.FISCAL_MONTH_DESC , TIMES.WEEK_ENDING_DAY, TIMES.TIME_ID ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.FISCAL_YEAR ,
          TIMES.FISCAL_QUARTER_DESC , TIMES.FISCAL_MONTH_DESC ,
          TIMES.WEEK_ENDING_DAY ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.FISCAL_YEAR ,
          TIMES.FISCAL_QUARTER_DESC , TIMES.FISCAL_MONTH_DESC ), 

          (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.CALENDAR_YEAR ,
           TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC ), 

          (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.FISCAL_YEAR ,
           TIMES.FISCAL_QUARTER_DESC ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.CALENDAR_YEAR ,
          TIMES.CALENDAR_QUARTER_DESC ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.FISCAL_YEAR ), 

         (PRODUCTS.PROD_TOTAL , PRODUCTS.PROD_CATEGORY , TIMES.CALENDAR_YEAR ), 

         (PRODUCTS.PROD_TOTAL , TIMES.CALENDAR_YEAR , 
          TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC ,
          TIMES.FISCAL_YEAR , TIMES.FISCAL_QUARTER_DESC , 
          TIMES.FISCAL_MONTH_DESC , TIMES.WEEK_ENDING_DAY , TIMES.TIME_ID ), 

          (PRODUCTS.PROD_TOTAL , TIMES.FISCAL_YEAR , TIMES.FISCAL_QUARTER_DESC ,
          TIMES.FISCAL_MONTH_DESC , TIMES.WEEK_ENDING_DAY ), 

         (PRODUCTS.PROD_TOTAL , TIMES.FISCAL_YEAR , TIMES.FISCAL_QUARTER_DESC ,
          TIMES.FISCAL_MONTH_DESC ), 

         (PRODUCTS.PROD_TOTAL , TIMES.CALENDAR_YEAR , 
          TIMES.CALENDAR_QUARTER_DESC , TIMES.CALENDAR_MONTH_DESC ), 

         (PRODUCTS.PROD_TOTAL , TIMES.FISCAL_YEAR ,   
          TIMES.FISCAL_QUARTER_DESC ), 

         (PRODUCTS.PROD_TOTAL, TIMES.CALENDAR_YEAR, 
          TIMES.CALENDAR_QUARTER_DESC),

         (PRODUCTS.PROD_TOTAL , TIMES.FISCAL_YEAR ), 

         (PRODUCTS.PROD_TOTAL , TIMES.CALENDAR_YEAR ) ) ;

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 BMHIDX_COST_PROD_TOTALTAL ON COST_CUBE_2_OLAP(PROD_TOTAL_77)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_PROD_CATEGORY ON COST_CUBE_2_OLAP
(PROD_CATEGORY_78)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_PROD_SUBCAORY ON COST_CUBE_2_OLAP
(PROD_SUBCATEGORY_79)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_PROD_ID_ID ON COST_CUBE_2_OLAP
(PROD_ID_80)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_CALENDAR_YEAR ON COST_CUBE_2_OLAP
(CALENDAR_YEAR_169)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_CALENDAR_QESC ON COST_CUBE_2_OLAP
(CALENDAR_QUARTER_DESC_170)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_CALENDAR_MESC ON COST_CUBE_2_OLAP
(CALENDAR_MONTH_DESC_171)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_FISCAL_YEAEAR ON COST_CUBE_2_OLAP
(FISCAL_YEAR_172)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_FISCAL_QUAESC ON COST_CUBE_2_OLAP
(FISCAL_QUARTER_DESC_173)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_FISCAL_MONESC ON COST_CUBE_2_OLAP
(FISCAL_MONTH_DESC_174)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_WEEK_ENDINDAY ON COST_CUBE_2_OLAP
(WEEK_ENDING_DAY_175)
LOCAL
COMPUTE STATISTICS
PARALLEL PCTFREE 0
NOLOGGING;

CREATE BITMAP INDEX BMHIDX_COST_TIME_ID_ID ON COST_CUBE_2_OLAP(TIME_ID_176)
LOCAL
COMPUTE STATISTICS
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', 'GROUPINGSET') ;