/*****************************************************************************
**  Oracle Summary Advisor  - Production
**
**  Summary Advisor Recommendation Script
*****************************************************************************/

/*****************************************************************************
**  Recommendations for run ID #21
*****************************************************************************/

/*****************************************************************************
**  Rank 1
**  Storage 97,385,020 bytes
**  Gain 37.92%
**  Benefit Ratio 513.49
**
**  SELECT sh.sales.prod_id, sh.products.prod_subcategory, 
**         sh.products.prod_category, sh.customers.cust_city, 
**         sh.customers.cust_state_province, sh.countries.country_id, 
**         sh.countries.country_subregion, sh.countries.country_region, 
**         count(*)
**      FROM sh.customers, sh.products, sh.sales, sh.countries
**      WHERE sh.sales.cust_id = sh.customers.cust_id
**        AND sh.sales.prod_id = sh.products.prod_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.sales.prod_id, sh.products.prod_subcategory, 
**               sh.products.prod_category, sh.customers.cust_city, 
**               sh.customers.cust_state_province, sh.countries.country_id, 
**               sh.countries.country_subregion, sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_1
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.SALES.PROD_ID, SH.PRODUCTS.PROD_SUBCATEGORY, SH.PRODUCTS.PROD_CATEGORY, SH.CUSTOMERS.CUST_CITY, 
SH.CUSTOMERS.CUST_STATE_PROVINCE, SH.COUNTRIES.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, 
SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, SH.PRODUCTS, SH.SALES, SH.COUNTRIES 
WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID 
AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.SALES.PROD_ID, SH.PRODUCTS.PROD_SUBCATEGORY, 
SH.PRODUCTS.PROD_CATEGORY, SH.CUSTOMERS.CUST_CITY, SH.CUSTOMERS.CUST_STATE_PROVINCE, 
SH.COUNTRIES.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 2
**  Storage 159,600 bytes
**  Gain 1.43%
**  Benefit Ratio 7,310.18
**
**  SELECT sh.products.prod_subcategory, sh.products.prod_category, 
**         sh.customers.country_id, sh.countries.country_subregion, 
**         sh.countries.country_region, count(*)
**      FROM sh.customers, sh.products, sh.sales, sh.countries
**      WHERE sh.sales.cust_id = sh.customers.cust_id
**        AND sh.sales.prod_id = sh.products.prod_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.products.prod_subcategory, sh.products.prod_category, 
**               sh.customers.country_id, sh.countries.country_subregion, 
**               sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_2
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.PRODUCTS.PROD_SUBCATEGORY, SH.PRODUCTS.PROD_CATEGORY, SH.CUSTOMERS.COUNTRY_ID, 
SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, 
SH.PRODUCTS, SH.SALES, SH.COUNTRIES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.SALES.PROD_ID 
= SH.PRODUCTS.PROD_ID AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY, 
SH.PRODUCTS.PROD_CATEGORY, SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, 
SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 3
**  Storage 1,562,085 bytes
**  Gain 7.60%
**  Benefit Ratio 3,924.37
**
**  SELECT sh.times.fiscal_year, sh.customers.cust_state_province, 
**         sh.customers.country_id, sh.countries.country_subregion, 
**         sh.countries.country_region, count(*)
**      FROM sh.customers, sh.times, sh.sales, sh.countries
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.times.fiscal_year, sh.customers.cust_state_province, 
**               sh.customers.country_id, sh.countries.country_subregion, 
**               sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_3
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, SH.CUSTOMERS.CUST_STATE_PROVINCE, SH.CUSTOMERS.COUNTRY_ID, 
SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, 
SH.TIMES, SH.SALES, SH.COUNTRIES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID 
= SH.CUSTOMERS.CUST_ID AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.TIMES.FISCAL_YEAR, 
SH.CUSTOMERS.CUST_STATE_PROVINCE, SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, 
SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 4
**  Storage 162,545 bytes
**  Gain 0.73%
**  Benefit Ratio 3,369.68
**
**  SELECT sh.customers.cust_state_province, sh.customers.country_id, 
**         sh.countries.country_subregion, sh.countries.country_region, 
**         count(*)
**      FROM sh.customers, sh.sales, sh.countries
**      WHERE sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.customers.cust_state_province, sh.customers.country_id, 
**               sh.countries.country_subregion, sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_4
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE, SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, 
SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, SH.SALES, SH.COUNTRIES WHERE SH.SALES.CUST_ID 
= SH.CUSTOMERS.CUST_ID AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.CUSTOMERS.CUST_STATE_PROVINCE, 
SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 5
**  Storage 74,844 bytes
**  Gain 0.26%
**  Benefit Ratio 2,603.18
**
**  SELECT sh.times.fiscal_quarter_desc, sh.times.fiscal_year, 
**         sh.countries.country_region, count(*)
**      FROM sh.customers, sh.times, sh.sales, sh.countries
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.times.fiscal_quarter_desc, sh.times.fiscal_year, 
**               sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_5
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR, SH.COUNTRIES.COUNTRY_REGION, 
COUNT(*) FROM SH.CUSTOMERS, SH.TIMES, SH.SALES, SH.COUNTRIES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID 
AND SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID 
GROUP BY SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR, SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 6
**  Storage 912 bytes
**  Gain 0.00%
**  Benefit Ratio 708.92
**
**  SELECT sh.customers.country_id, sh.countries.country_subregion, 
**         sh.countries.country_region, count(*)
**      FROM sh.customers, sh.sales, sh.countries
**      WHERE sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.customers.country_id, sh.countries.country_subregion, 
**               sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_6
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION, 
COUNT(*) FROM SH.CUSTOMERS, SH.SALES, SH.COUNTRIES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID 
AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.CUSTOMERS.COUNTRY_ID, 
SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 7
**  Storage 8,892 bytes
**  Gain 0.01%
**  Benefit Ratio 531.44
**
**  SELECT sh.times.fiscal_year, sh.customers.country_id, 
**         sh.countries.country_subregion, sh.countries.country_region, 
**         count(*)
**      FROM sh.customers, sh.times, sh.sales, sh.countries
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.times.fiscal_year, sh.customers.country_id, 
**               sh.countries.country_subregion, sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_7
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, 
SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, SH.TIMES, SH.SALES, SH.COUNTRIES 
WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND 
SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.TIMES.FISCAL_YEAR, SH.CUSTOMERS.COUNTRY_ID, 
SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 8
**  Storage 7,904 bytes
**  Gain 0.01%
**  Benefit Ratio 524.02
**
**  SELECT sh.times.calendar_year, sh.customers.country_id, 
**         sh.countries.country_subregion, sh.countries.country_region, 
**         count(*)
**      FROM sh.customers, sh.times, sh.sales, sh.countries
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.times.calendar_year, sh.customers.country_id, 
**               sh.countries.country_subregion, sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_8
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_YEAR, SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, 
SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, SH.TIMES, SH.SALES, SH.COUNTRIES 
WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND 
SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.TIMES.CALENDAR_YEAR, SH.CUSTOMERS.COUNTRY_ID, 
SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 9
**  Storage 10,098 bytes
**  Gain 0.00%
**  Benefit Ratio 356.47
**
**  SELECT sh.times.fiscal_quarter_desc, sh.times.fiscal_year, count(*)
**      FROM sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**      GROUP BY sh.times.fiscal_quarter_desc, sh.times.fiscal_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_9
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR, COUNT(*) FROM SH.TIMES, SH.SALES 
WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID GROUP BY SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR;

/*****************************************************************************
**  Rank 10
**  Storage 8,704 bytes
**  Gain 0.00%
**  Benefit Ratio 352.76
**
**  SELECT sh.times.calendar_quarter_desc, sh.times.calendar_year, 
**         count(*)
**      FROM sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**      GROUP BY sh.times.calendar_quarter_desc, sh.times.calendar_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_10
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_QUARTER_DESC, SH.TIMES.CALENDAR_YEAR, COUNT(*) FROM SH.TIMES, 
SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID GROUP BY SH.TIMES.CALENDAR_QUARTER_DESC, 
SH.TIMES.CALENDAR_YEAR;

/*****************************************************************************
**  Rank 11
**  Storage 10,098 bytes
**  Gain 0.00%
**  Benefit Ratio 334.52
**
**  SELECT sh.times.fiscal_quarter_desc, sh.times.fiscal_year, count(*)
**      FROM sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**      GROUP BY sh.times.fiscal_quarter_desc, sh.times.fiscal_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_11
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR, COUNT(*) FROM SH.TIMES, SH.COSTS 
WHERE SH.COSTS.TIME_ID = SH.TIMES.TIME_ID GROUP BY SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR;

/*****************************************************************************
**  Rank 12
**  Storage 8,704 bytes
**  Gain 0.00%
**  Benefit Ratio 339.34
**
**  SELECT sh.times.calendar_quarter_desc, sh.times.calendar_year, 
**         count(*)
**      FROM sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**      GROUP BY sh.times.calendar_quarter_desc, sh.times.calendar_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_12
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_QUARTER_DESC, SH.TIMES.CALENDAR_YEAR, COUNT(*) FROM SH.TIMES, 
SH.COSTS WHERE SH.COSTS.TIME_ID = SH.TIMES.TIME_ID GROUP BY SH.TIMES.CALENDAR_QUARTER_DESC, 
SH.TIMES.CALENDAR_YEAR;

/*****************************************************************************
**  Rank 13
**  Storage 54,810 bytes
**  Gain 0.01%
**  Benefit Ratio 109.66
**
**  SELECT sh.times.fiscal_year, sh.products.prod_subcategory, 
**         sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.prod_id = sh.products.prod_id
**      GROUP BY sh.times.fiscal_year, sh.products.prod_subcategory, 
**               sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_13
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, SH.PRODUCTS.PROD_CATEGORY, 
COUNT(*) FROM SH.PRODUCTS, SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND 
SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, 
SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 14
**  Storage 48,720 bytes
**  Gain 0.01%
**  Benefit Ratio 106.24
**
**  SELECT sh.times.calendar_year, sh.products.prod_subcategory, 
**         sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.prod_id = sh.products.prod_id
**      GROUP BY sh.times.calendar_year, sh.products.prod_subcategory, 
**               sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_14
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, SH.PRODUCTS.PROD_CATEGORY, 
COUNT(*) FROM SH.PRODUCTS, SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND 
SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, 
SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 15
**  Storage 54,810 bytes
**  Gain 0.01%
**  Benefit Ratio 101.16
**
**  SELECT sh.times.fiscal_year, sh.products.prod_subcategory, 
**         sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**        AND sh.costs.prod_id = sh.products.prod_id
**      GROUP BY sh.times.fiscal_year, sh.products.prod_subcategory, 
**               sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_15
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, SH.PRODUCTS.PROD_CATEGORY, 
COUNT(*) FROM SH.PRODUCTS, SH.TIMES, SH.COSTS WHERE SH.COSTS.TIME_ID = SH.TIMES.TIME_ID AND 
SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, 
SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 16
**  Storage 48,720 bytes
**  Gain 0.01%
**  Benefit Ratio 100.80
**
**  SELECT sh.times.calendar_year, sh.products.prod_subcategory, 
**         sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**        AND sh.costs.prod_id = sh.products.prod_id
**      GROUP BY sh.times.calendar_year, sh.products.prod_subcategory, 
**               sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_16
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, SH.PRODUCTS.PROD_CATEGORY, 
COUNT(*) FROM SH.PRODUCTS, SH.TIMES, SH.COSTS WHERE SH.COSTS.TIME_ID = SH.TIMES.TIME_ID AND 
SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_SUBCATEGORY, 
SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 17
**  Storage 6,270 bytes
**  Gain 0.00%
**  Benefit Ratio 97.82
**
**  SELECT sh.products.prod_category, sh.customers.country_id, 
**         sh.countries.country_subregion, sh.countries.country_region, 
**         count(*)
**      FROM sh.customers, sh.products, sh.sales, sh.countries
**      WHERE sh.sales.cust_id = sh.customers.cust_id
**        AND sh.sales.prod_id = sh.products.prod_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.products.prod_category, sh.customers.country_id, 
**               sh.countries.country_subregion, sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_17
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.PRODUCTS.PROD_CATEGORY, SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, 
SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, SH.PRODUCTS, SH.SALES, SH.COUNTRIES 
WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID 
AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.PRODUCTS.PROD_CATEGORY, 
SH.CUSTOMERS.COUNTRY_ID, SH.COUNTRIES.COUNTRY_SUBREGION, SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 18
**  Storage 77,220 bytes
**  Gain 0.01%
**  Benefit Ratio 51.31
**
**  SELECT sh.times.fiscal_quarter_desc, sh.times.fiscal_year, 
**         sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.prod_id = sh.products.prod_id
**      GROUP BY sh.times.fiscal_quarter_desc, sh.times.fiscal_year, 
**               sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_18
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_CATEGORY, 
COUNT(*) FROM SH.PRODUCTS, SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND 
SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.TIMES.FISCAL_QUARTER_DESC, SH.TIMES.FISCAL_YEAR, 
SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 19
**  Storage 66,560 bytes
**  Gain 0.00%
**  Benefit Ratio 51.31
**
**  SELECT sh.times.calendar_quarter_desc, sh.times.calendar_year, 
**         sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.prod_id = sh.products.prod_id
**      GROUP BY sh.times.calendar_quarter_desc, sh.times.calendar_year, 
**               sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_19
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_QUARTER_DESC, SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_CATEGORY, 
COUNT(*) FROM SH.PRODUCTS, SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND 
SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.TIMES.CALENDAR_QUARTER_DESC, SH.TIMES.CALENDAR_YEAR, 
SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 21
**  Storage 180 bytes
**  Gain 0.00%
**  Benefit Ratio 4.07
**
**  SELECT sh.countries.country_region, count(*)
**      FROM sh.customers, sh.sales, sh.countries
**      WHERE sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_21
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, SH.SALES, SH.COUNTRIES 
WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID 
GROUP BY SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 22
**  Storage 1,440 bytes
**  Gain 0.00%
**  Benefit Ratio 3.35
**
**  SELECT sh.products.prod_category, sh.countries.country_region, 
**         count(*)
**      FROM sh.customers, sh.products, sh.sales, sh.countries
**      WHERE sh.sales.cust_id = sh.customers.cust_id
**        AND sh.sales.prod_id = sh.products.prod_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.products.prod_category, sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_22
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.PRODUCTS.PROD_CATEGORY, SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, 
SH.PRODUCTS, SH.SALES, SH.COUNTRIES WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID AND SH.SALES.PROD_ID 
= SH.PRODUCTS.PROD_ID AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.PRODUCTS.PROD_CATEGORY, 
SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 23
**  Storage 208 bytes
**  Gain 0.00%
**  Benefit Ratio 40.85
**
**  SELECT sh.times.calendar_year, count(*)
**      FROM sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**      GROUP BY sh.times.calendar_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_23
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_YEAR, COUNT(*) FROM SH.TIMES, SH.COSTS WHERE SH.COSTS.TIME_ID = 
SH.TIMES.TIME_ID GROUP BY SH.TIMES.CALENDAR_YEAR;

/*****************************************************************************
**  Rank 24
**  Storage 208 bytes
**  Gain 0.00%
**  Benefit Ratio 40.85
**
**  SELECT sh.times.calendar_year, count(*)
**      FROM sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**      GROUP BY sh.times.calendar_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_24
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_YEAR, COUNT(*) FROM SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID = 
SH.TIMES.TIME_ID GROUP BY SH.TIMES.CALENDAR_YEAR;

/*****************************************************************************
**  Rank 25
**  Storage 1,760 bytes
**  Gain 0.00%
**  Benefit Ratio 26.68
**
**  SELECT sh.times.calendar_year, sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**        AND sh.costs.prod_id = sh.products.prod_id
**      GROUP BY sh.times.calendar_year, sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_25
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_CATEGORY, COUNT(*) FROM SH.PRODUCTS, SH.TIMES, 
SH.COSTS WHERE SH.COSTS.TIME_ID = SH.TIMES.TIME_ID AND SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID 
GROUP BY SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 26
**  Storage 1,760 bytes
**  Gain 0.00%
**  Benefit Ratio 26.68
**
**  SELECT sh.times.calendar_year, sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.prod_id = sh.products.prod_id
**      GROUP BY sh.times.calendar_year, sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_26
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_CATEGORY, COUNT(*) FROM SH.PRODUCTS, SH.TIMES, 
SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID 
GROUP BY SH.TIMES.CALENDAR_YEAR, SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 27
**  Storage 234 bytes
**  Gain 0.00%
**  Benefit Ratio 42.15
**
**  SELECT sh.times.fiscal_year, count(*)
**      FROM sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**      GROUP BY sh.times.fiscal_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_27
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, COUNT(*) FROM SH.TIMES, SH.COSTS WHERE SH.COSTS.TIME_ID = SH.TIMES.TIME_ID 
GROUP BY SH.TIMES.FISCAL_YEAR;

/*****************************************************************************
**  Rank 28
**  Storage 234 bytes
**  Gain 0.00%
**  Benefit Ratio 42.15
**
**  SELECT sh.times.fiscal_year, count(*)
**      FROM sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**      GROUP BY sh.times.fiscal_year
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_28
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, COUNT(*) FROM SH.TIMES, SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID 
GROUP BY SH.TIMES.FISCAL_YEAR;

/*****************************************************************************
**  Rank 29
**  Storage 1,836 bytes
**  Gain 0.00%
**  Benefit Ratio 3.84
**
**  SELECT sh.times.fiscal_year, sh.countries.country_region, count(*)
**      FROM sh.customers, sh.times, sh.sales, sh.countries
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.cust_id = sh.customers.cust_id
**        AND sh.customers.country_id = sh.countries.country_id
**      GROUP BY sh.times.fiscal_year, sh.countries.country_region
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_29
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, SH.COUNTRIES.COUNTRY_REGION, COUNT(*) FROM SH.CUSTOMERS, 
SH.TIMES, SH.SALES, SH.COUNTRIES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID 
= SH.CUSTOMERS.CUST_ID AND SH.CUSTOMERS.COUNTRY_ID = SH.COUNTRIES.COUNTRY_ID GROUP BY SH.TIMES.FISCAL_YEAR, 
SH.COUNTRIES.COUNTRY_REGION;

/*****************************************************************************
**  Rank 30
**  Storage 1,980 bytes
**  Gain 0.00%
**  Benefit Ratio 26.68
**
**  SELECT sh.times.fiscal_year, sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.costs
**      WHERE sh.costs.time_id = sh.times.time_id
**        AND sh.costs.prod_id = sh.products.prod_id
**      GROUP BY sh.times.fiscal_year, sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_30
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_CATEGORY, COUNT(*) FROM SH.PRODUCTS, SH.TIMES, 
SH.COSTS WHERE SH.COSTS.TIME_ID = SH.TIMES.TIME_ID AND SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID 
GROUP BY SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_CATEGORY;

/*****************************************************************************
**  Rank 31
**  Storage 1,980 bytes
**  Gain 0.00%
**  Benefit Ratio 26.68
**
**  SELECT sh.times.fiscal_year, sh.products.prod_category, count(*)
**      FROM sh.products, sh.times, sh.sales
**      WHERE sh.sales.time_id = sh.times.time_id
**        AND sh.sales.prod_id = sh.products.prod_id
**      GROUP BY sh.times.fiscal_year, sh.products.prod_category
*****************************************************************************/

CREATE MATERIALIZED VIEW mv_id_21_rank_31
  TABLESPACE demo
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE AS
    SELECT SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_CATEGORY, COUNT(*) FROM SH.PRODUCTS, SH.TIMES, 
SH.SALES WHERE SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID 
GROUP BY SH.TIMES.FISCAL_YEAR, SH.PRODUCTS.PROD_CATEGORY;
