Rem SQL Access Advisor: Version 10.2.0.2.0 - Production
Rem 
Rem Username: SH_MV
Rem Task: SQLACCESS7121382
Rem Execution date: 06/09/2006 18:47
Rem 

spool c:\agg_test_files\init_agg_sh_mv.log

prompt Initial aggregation of the SH_MV data

connect sh_mv/password@ora10g

variable start_time varchar2(20)
variable end_time varchar2(20)

begin
select to_char(sysdate, 'DD:MM:YYYY HH:MI:SS') into :start_time from dual;
end;
/


CREATE MATERIALIZED VIEW LOG ON
"SH_MV"."TIMES"
WITH ROWID, SEQUENCE("TIME_ID","WEEK_ENDING_DAY_ID","CALENDAR_MONTH_ID","FISCAL_MONTH_ID","CALENDAR_QUARTER_ID","FISCAL_QUARTER_ID","CALENDAR_YEAR_ID","FISCAL_YEAR_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH_MV"."COUNTRIES"
WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_SUBREGION_ID","COUNTRY_REGION_ID","COUNTRY_TOTAL_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH_MV"."CUSTOMERS"
WITH ROWID, SEQUENCE("CUST_ID","CUST_CITY_ID","CUST_STATE_PROVINCE_ID","COUNTRY_ID","CUST_TOTAL_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH_MV"."PROMOTIONS"
WITH ROWID, SEQUENCE("PROMO_ID","PROMO_TOTAL_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH_MV"."CHANNELS"
WITH ROWID, SEQUENCE("CHANNEL_ID","CHANNEL_CLASS_ID","CHANNEL_TOTAL_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH_MV"."PRODUCTS"
WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY_ID","PROD_CATEGORY_ID","PROD_TOTAL_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH_MV"."SALES"
WITH ROWID, SEQUENCE("PROD_ID","CUST_ID","TIME_ID","CHANNEL_ID","PROMO_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 
SELECT  SH_MV.CUSTOMERS.CUST_TOTAL_ID C1
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID C3
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID C4
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID C5
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID C6
, 	SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID C7
, 	SH_MV.SALES.CHANNEL_ID C8
, 	SH_MV.SALES.PROD_ID C9
, 	COUNT(*) M1 
FROM 	SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
, 	SH_MV.CHANNELS
, 	SH_MV.PRODUCTS
, 	SH_MV.SALES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID 
AND 	SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID 
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND	SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID 
GROUP BY SH_MV.CUSTOMERS.CUST_TOTAL_ID
,	 SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	 SH_MV.CHANNELS.CHANNEL_TOTAL_ID
,	 SH_MV.CHANNELS.CHANNEL_CLASS_ID
,	 SH_MV.PRODUCTS.PROD_TOTAL_ID
, 	 SH_MV.PRODUCTS.PROD_CATEGORY_ID
, 	 SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID
,	 SH_MV.SALES.CHANNEL_ID
, 	 SH_MV.SALES.PROD_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200000"',NULL,dbms_stats.auto_sample_size);
	end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200001"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 
SELECT 	SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1
, 	SH_MV.COUNTRIES.COUNTRY_REGION_ID C2
, 	SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID C3
, 	SH_MV.CUSTOMERS.COUNTRY_ID C4
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C5
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID C6
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID C7
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID C8
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID C9
, 	SH_MV.SALES.CHANNEL_ID C10
, 	COUNT(*) M1 
FROM 	SH_MV.CUSTOMERS
,	SH_MV.PROMOTIONS
,	SH_MV.CHANNELS
, 	SH_MV.PRODUCTS
, 	SH_MV.SALES
, 	SH_MV.COUNTRIES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID 
AND 	SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND 	SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
AND 	SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID 
GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID
,	 SH_MV.COUNTRIES.COUNTRY_REGION_ID
, 	 SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
, 	 SH_MV.CUSTOMERS.COUNTRY_ID
,	 SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	 SH_MV.CHANNELS.CHANNEL_TOTAL_ID
, 	 SH_MV.CHANNELS.CHANNEL_CLASS_ID
, 	 SH_MV.PRODUCTS.PROD_TOTAL_ID
,	 SH_MV.PRODUCTS.PROD_CATEGORY_ID
, 	 SH_MV.SALES.CHANNEL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200001"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200002"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 
SELECT 	SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID C3
,       SH_MV.CHANNELS.CHANNEL_CLASS_ID C4
,       SH_MV.PRODUCTS.PROD_TOTAL_ID C5
,       SH_MV.PRODUCTS.PROD_CATEGORY_ID C6
,       SH_MV.SALES.CHANNEL_ID C7
,       COUNT(*) M1 FROM SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
,	SH_MV.CHANNELS
, 	SH_MV.PRODUCTS
,	SH_MV.SALES
, 	SH_MV.COUNTRIES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID 
AND 	SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND 	SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
AND 	SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID 
GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID
,	SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID
,	SH_MV.PRODUCTS.PROD_TOTAL_ID
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID
, 	SH_MV.SALES.CHANNEL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200002"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE 	MATERIALIZED VIEW "SH_MV"."MV$$_04200003"
REFRESH FAST WITH ROWID
ENABLE 	QUERY REWRITE
AS 	SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1
, 	SH_MV.COUNTRIES.COUNTRY_REGION_ID C2
, 	SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID C3
, 	SH_MV.CUSTOMERS.COUNTRY_ID C4
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C5
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID C6
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID C7
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID C8
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID C9
, 	SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID C10
, 	SH_MV.SALES.CHANNEL_ID C11
, 	SH_MV.SALES.PROD_ID C12
, 	COUNT(*) M1 FROM SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
, 	SH_MV.CHANNELS
, 	SH_MV.PRODUCTS
,	SH_MV.SALES
, 	SH_MV.COUNTRIES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND 	SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID 
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND 	SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID 
AND 	SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID 
GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID
, 	SH_MV.COUNTRIES.COUNTRY_REGION_ID
, 	SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
, 	SH_MV.CUSTOMERS.COUNTRY_ID
,	SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID
,	SH_MV.PRODUCTS.PROD_TOTAL_ID
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID
, 	SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID
,	SH_MV.SALES.CHANNEL_ID, SH_MV.SALES.PROD_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200003"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200005"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 	
SELECT 	SH_MV.CUSTOMERS.CUST_TOTAL_ID C1
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID C3
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID C4
, 	COUNT(*) M1 FROM SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
,	SH_MV.PRODUCTS
, 	SH_MV.SALES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND 	SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
GROUP BY SH_MV.CUSTOMERS.CUST_TOTAL_ID
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID
,	SH_MV.PRODUCTS.PROD_CATEGORY_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200005"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200006"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.PRODUCTS.PROD_TOTAL_ID
C3, SH_MV.PRODUCTS.PROD_CATEGORY_ID C4, SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID C5,
SH_MV.SALES.PROD_ID C6, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.PRODUCTS,
SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.PRODUCTS.PROD_TOTAL_ID, SH_MV.PRODUCTS.PROD_CATEGORY_ID,
SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID, SH_MV.SALES.PROD_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200006"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200007"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 	
SELECT 	SH_MV.CUSTOMERS.CUST_TOTAL_ID C1
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID C3
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID C4
,	SH_MV.SALES.CHANNEL_ID C5
, 	COUNT(*) M1 
FROM 	SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
, 	SH_MV.CHANNELS
, 	SH_MV.SALES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID 
AND 	SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID 
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
GROUP BY SH_MV.CUSTOMERS.CUST_TOTAL_ID
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID
,	SH_MV.SALES.CHANNEL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200007"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200008"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 
SELECT  SH_MV.CUSTOMERS.CUST_TOTAL_ID C1
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2
, 	SH_MV.CHANNELS.CHANNEL_TOTAL_ID C3
, 	SH_MV.CHANNELS.CHANNEL_CLASS_ID C4
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID C5
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID C6
, 	SH_MV.TIMES.FISCAL_YEAR_ID C7
, 	SH_MV.TIMES.CALENDAR_YEAR_ID C8
, 	SH_MV.TIMES.FISCAL_QUARTER_ID C9
, 	SH_MV.TIMES.CALENDAR_QUARTER_ID C10
, 	SH_MV.TIMES.FISCAL_MONTH_ID C11
, 	SH_MV.TIMES.CALENDAR_MONTH_ID C12
, 	SH_MV.TIMES.WEEK_ENDING_DAY_ID C13
, 	SH_MV.SALES.CHANNEL_ID C14
, 	SH_MV.SALES.TIME_ID C15
, 	COUNT(*) M1 
FROM 	SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
, 	SH_MV.CHANNELS
, 	SH_MV.PRODUCTS
, 	SH_MV.TIMES
, 	SH_MV.SALES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID 
AND 	SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID 
AND 	SH_MV.SALES.TIME_ID = SH_MV.TIMES.TIME_ID
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND 	SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
GROUP BY SH_MV.CUSTOMERS.CUST_TOTAL_ID
, 	 SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	 SH_MV.CHANNELS.CHANNEL_TOTAL_ID
,	 SH_MV.CHANNELS.CHANNEL_CLASS_ID
, 	 SH_MV.PRODUCTS.PROD_TOTAL_ID
, 	 SH_MV.PRODUCTS.PROD_CATEGORY_ID
,	 SH_MV.TIMES.FISCAL_YEAR_ID
, 	 SH_MV.TIMES.CALENDAR_YEAR_ID
, 	 SH_MV.TIMES.FISCAL_QUARTER_ID
, 	 SH_MV.TIMES.CALENDAR_QUARTER_ID
, 	 SH_MV.TIMES.FISCAL_MONTH_ID
, 	 SH_MV.TIMES.CALENDAR_MONTH_ID
,	 SH_MV.TIMES.WEEK_ENDING_DAY_ID
, 	 SH_MV.SALES.CHANNEL_ID
, 	 SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200008"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200009"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 
SELECT 	SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2
, 	COUNT(*) M1 FROM SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
, 	SH_MV.SALES
, 	SH_MV.COUNTRIES 
WHERE 	SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID 
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND 	SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID 
GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID
,	SH_MV.PROMOTIONS.PROMO_TOTAL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200009"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420000B"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 
SELECT 	SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1
, 	SH_MV.COUNTRIES.COUNTRY_REGION_ID C2
, 	SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID C3
, 	SH_MV.CUSTOMERS.COUNTRY_ID C4
, 	SH_MV.PROMOTIONS.PROMO_TOTAL_ID C5
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID C6
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID C7
,	COUNT(*) M1 FROM SH_MV.CUSTOMERS
, 	SH_MV.PROMOTIONS
,	SH_MV.PRODUCTS
, 	SH_MV.SALES
, 	SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND 	SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID 
AND 	SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
AND 	SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID 
GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID
, 	SH_MV.COUNTRIES.COUNTRY_REGION_ID
, 	SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
, 	SH_MV.CUSTOMERS.COUNTRY_ID
,	SH_MV.PROMOTIONS.PROMO_TOTAL_ID
, 	SH_MV.PRODUCTS.PROD_TOTAL_ID
, 	SH_MV.PRODUCTS.PROD_CATEGORY_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420000B"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420000C"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.CHANNELS.CHANNEL_TOTAL_ID
C3, SH_MV.CHANNELS.CHANNEL_CLASS_ID C4, SH_MV.PRODUCTS.PROD_TOTAL_ID C5, SH_MV.PRODUCTS.PROD_CATEGORY_ID
C6, SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID C7, SH_MV.SALES.CHANNEL_ID C8, SH_MV.SALES.PROD_ID
C9, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.CHANNELS, SH_MV.PRODUCTS,
SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID AND SH_MV.SALES.CUST_ID
= SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID AND
SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_CLASS_ID,
SH_MV.PRODUCTS.PROD_TOTAL_ID, SH_MV.PRODUCTS.PROD_CATEGORY_ID, SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID,
SH_MV.SALES.CHANNEL_ID, SH_MV.SALES.PROD_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420000C"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420000E"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.COUNTRIES.COUNTRY_REGION_ID C2, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
C3, SH_MV.CUSTOMERS.CUST_TOTAL_ID C4, SH_MV.CUSTOMERS.COUNTRY_ID C5, SH_MV.CUSTOMERS.CUST_STATE_PROVINCE_ID
C6, SH_MV.CUSTOMERS.CUST_CITY_ID C7, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C8, SH_MV.SALES.CUST_ID
C9, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.SALES, SH_MV.COUNTRIES
WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.CUST_ID
= SH_MV.CUSTOMERS.CUST_ID AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID
GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID, SH_MV.COUNTRIES.COUNTRY_REGION_ID,
SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID, SH_MV.CUSTOMERS.CUST_TOTAL_ID, SH_MV.CUSTOMERS.COUNTRY_ID,
SH_MV.CUSTOMERS.CUST_STATE_PROVINCE_ID, SH_MV.CUSTOMERS.CUST_CITY_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID,
SH_MV.SALES.CUST_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420000E"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420000F"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.CUSTOMERS.CUST_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.CHANNELS.CHANNEL_TOTAL_ID
C3, SH_MV.CHANNELS.CHANNEL_CLASS_ID C4, SH_MV.PRODUCTS.PROD_TOTAL_ID C5, SH_MV.PRODUCTS.PROD_CATEGORY_ID
C6, SH_MV.SALES.CHANNEL_ID C7, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS,
SH_MV.CHANNELS, SH_MV.PRODUCTS, SH_MV.SALES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID AND SH_MV.SALES.CUST_ID
= SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID GROUP
BY SH_MV.CUSTOMERS.CUST_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_TOTAL_ID,
SH_MV.CHANNELS.CHANNEL_CLASS_ID, SH_MV.PRODUCTS.PROD_TOTAL_ID, SH_MV.PRODUCTS.PROD_CATEGORY_ID,
SH_MV.SALES.CHANNEL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420000F"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200010"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.PRODUCTS.PROD_TOTAL_ID
C3, SH_MV.PRODUCTS.PROD_CATEGORY_ID C4, SH_MV.TIMES.FISCAL_YEAR_ID C5, SH_MV.TIMES.CALENDAR_YEAR_ID
C6, SH_MV.TIMES.FISCAL_QUARTER_ID C7, SH_MV.TIMES.CALENDAR_QUARTER_ID C8, SH_MV.TIMES.FISCAL_MONTH_ID
C9, SH_MV.TIMES.CALENDAR_MONTH_ID C10, SH_MV.TIMES.WEEK_ENDING_DAY_ID C11, SH_MV.SALES.TIME_ID
C12, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.PRODUCTS, SH_MV.TIMES,
SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.TIME_ID = SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID
AND SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID AND SH_MV.CUSTOMERS.COUNTRY_ID
= SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID,
SH_MV.PRODUCTS.PROD_TOTAL_ID, SH_MV.PRODUCTS.PROD_CATEGORY_ID, SH_MV.TIMES.FISCAL_YEAR_ID,
SH_MV.TIMES.CALENDAR_YEAR_ID, SH_MV.TIMES.FISCAL_QUARTER_ID, SH_MV.TIMES.CALENDAR_QUARTER_ID,
SH_MV.TIMES.FISCAL_MONTH_ID, SH_MV.TIMES.CALENDAR_MONTH_ID, SH_MV.TIMES.WEEK_ENDING_DAY_ID,
SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200010"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200012"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.COUNTRIES.COUNTRY_REGION_ID C2, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
C3, SH_MV.CUSTOMERS.COUNTRY_ID C4, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C5, SH_MV.PRODUCTS.PROD_TOTAL_ID
C6, SH_MV.PRODUCTS.PROD_CATEGORY_ID C7, SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID C8,
SH_MV.SALES.PROD_ID C9, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.PRODUCTS,
SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.COUNTRIES.COUNTRY_REGION_ID, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID, SH_MV.CUSTOMERS.COUNTRY_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.PRODUCTS.PROD_TOTAL_ID, SH_MV.PRODUCTS.PROD_CATEGORY_ID,
SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID, SH_MV.SALES.PROD_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200012"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200013"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.COUNTRIES.COUNTRY_REGION_ID C2, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
C3, SH_MV.CUSTOMERS.COUNTRY_ID C4, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C5, SH_MV.CHANNELS.CHANNEL_TOTAL_ID
C6, SH_MV.CHANNELS.CHANNEL_CLASS_ID C7, SH_MV.SALES.CHANNEL_ID C8, COUNT(*) M1 
FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.CHANNELS, SH_MV.SALES, SH_MV.COUNTRIES
WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.CHANNEL_ID
= SH_MV.CHANNELS.CHANNEL_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND
SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.COUNTRIES.COUNTRY_REGION_ID, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID, SH_MV.CUSTOMERS.COUNTRY_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_CLASS_ID,
SH_MV.SALES.CHANNEL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200013"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200014"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.COUNTRIES.COUNTRY_REGION_ID C2, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
C3, SH_MV.CUSTOMERS.COUNTRY_ID C4, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C5
, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.SALES, SH_MV.COUNTRIES 
WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND
SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.COUNTRIES.COUNTRY_REGION_ID, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID, SH_MV.CUSTOMERS.COUNTRY_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200014"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200015"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.CUSTOMERS.CUST_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.PRODUCTS.PROD_TOTAL_ID
C3, SH_MV.PRODUCTS.PROD_CATEGORY_ID C4, SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID C5,
SH_MV.SALES.PROD_ID C6, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.PRODUCTS,
SH_MV.SALES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.CUST_ID
= SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID GROUP
BY SH_MV.CUSTOMERS.CUST_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.PRODUCTS.PROD_TOTAL_ID,
SH_MV.PRODUCTS.PROD_CATEGORY_ID, SH_MV.PRODUCTS.PROD_SUBCATEGORY_ID, SH_MV.SALES.PROD_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200015"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200016"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.CUSTOMERS.CUST_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2
, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.SALES WHERE SH_MV.SALES.PROMO_ID
= SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID GROUP
BY SH_MV.CUSTOMERS.CUST_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200016"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200017"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.PRODUCTS.PROD_TOTAL_ID
C3, SH_MV.PRODUCTS.PROD_CATEGORY_ID C4, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS,
SH_MV.PRODUCTS, SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID = SH_MV.PRODUCTS.PROD_ID
AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.PRODUCTS.PROD_TOTAL_ID, SH_MV.PRODUCTS.PROD_CATEGORY_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200017"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200018"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.CHANNELS.CHANNEL_TOTAL_ID
C3, SH_MV.CHANNELS.CHANNEL_CLASS_ID C4, SH_MV.SALES.CHANNEL_ID C5, COUNT(*) M1 
FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.CHANNELS, SH_MV.SALES, SH_MV.COUNTRIES
WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.CHANNEL_ID
= SH_MV.CHANNELS.CHANNEL_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND
SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_CLASS_ID,
SH_MV.SALES.CHANNEL_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200018"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420001A"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.CHANNELS.CHANNEL_TOTAL_ID
C3, SH_MV.CHANNELS.CHANNEL_CLASS_ID C4, SH_MV.TIMES.FISCAL_YEAR_ID C5, SH_MV.TIMES.CALENDAR_YEAR_ID
C6, SH_MV.TIMES.FISCAL_QUARTER_ID C7, SH_MV.TIMES.CALENDAR_QUARTER_ID C8, SH_MV.TIMES.FISCAL_MONTH_ID
C9, SH_MV.TIMES.CALENDAR_MONTH_ID C10, SH_MV.TIMES.WEEK_ENDING_DAY_ID C11, SH_MV.SALES.CHANNEL_ID
C12, SH_MV.SALES.TIME_ID C13, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS,
SH_MV.CHANNELS, SH_MV.TIMES, SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID
= SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID
AND SH_MV.SALES.TIME_ID = SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID
AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_CLASS_ID,
SH_MV.TIMES.FISCAL_YEAR_ID, SH_MV.TIMES.CALENDAR_YEAR_ID, SH_MV.TIMES.FISCAL_QUARTER_ID,
SH_MV.TIMES.CALENDAR_QUARTER_ID, SH_MV.TIMES.FISCAL_MONTH_ID, SH_MV.TIMES.CALENDAR_MONTH_ID,
SH_MV.TIMES.WEEK_ENDING_DAY_ID, SH_MV.SALES.CHANNEL_ID, SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420001A"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420001B"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.CUSTOMERS.CUST_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.TIMES.FISCAL_YEAR_ID
C3, SH_MV.TIMES.CALENDAR_YEAR_ID C4, SH_MV.TIMES.FISCAL_QUARTER_ID C5, SH_MV.TIMES.CALENDAR_QUARTER_ID
C6, SH_MV.TIMES.FISCAL_MONTH_ID C7, SH_MV.TIMES.CALENDAR_MONTH_ID C8, SH_MV.TIMES.WEEK_ENDING_DAY_ID
C9, SH_MV.SALES.TIME_ID C10, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS,
SH_MV.TIMES, SH_MV.SALES 
WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID AND
SH_MV.SALES.TIME_ID = SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID
GROUP BY SH_MV.CUSTOMERS.CUST_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.TIMES.FISCAL_YEAR_ID,
SH_MV.TIMES.CALENDAR_YEAR_ID, SH_MV.TIMES.FISCAL_QUARTER_ID, SH_MV.TIMES.CALENDAR_QUARTER_ID,
SH_MV.TIMES.FISCAL_MONTH_ID, SH_MV.TIMES.CALENDAR_MONTH_ID, SH_MV.TIMES.WEEK_ENDING_DAY_ID,
SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420001B"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420001C"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.COUNTRIES.COUNTRY_REGION_ID C2, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID
C3, SH_MV.CUSTOMERS.COUNTRY_ID C4, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C5, SH_MV.TIMES.FISCAL_YEAR_ID
C6, SH_MV.TIMES.CALENDAR_YEAR_ID C7, SH_MV.TIMES.FISCAL_QUARTER_ID C8, SH_MV.TIMES.CALENDAR_QUARTER_ID
C9, SH_MV.TIMES.FISCAL_MONTH_ID C10, SH_MV.TIMES.CALENDAR_MONTH_ID C11, SH_MV.TIMES.WEEK_ENDING_DAY_ID
C12, SH_MV.SALES.TIME_ID C13, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS,
SH_MV.TIMES, SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.TIME_ID = SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID
AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.COUNTRIES.COUNTRY_REGION_ID, SH_MV.COUNTRIES.COUNTRY_SUBREGION_ID, SH_MV.CUSTOMERS.COUNTRY_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.TIMES.FISCAL_YEAR_ID, SH_MV.TIMES.CALENDAR_YEAR_ID,
SH_MV.TIMES.FISCAL_QUARTER_ID, SH_MV.TIMES.CALENDAR_QUARTER_ID, SH_MV.TIMES.FISCAL_MONTH_ID,
SH_MV.TIMES.CALENDAR_MONTH_ID, SH_MV.TIMES.WEEK_ENDING_DAY_ID, SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420001C"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420001D"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.CHANNELS.CHANNEL_TOTAL_ID
C3, SH_MV.CHANNELS.CHANNEL_CLASS_ID C4, SH_MV.PRODUCTS.PROD_TOTAL_ID C5, SH_MV.PRODUCTS.PROD_CATEGORY_ID
C6, SH_MV.TIMES.FISCAL_YEAR_ID C7, SH_MV.TIMES.CALENDAR_YEAR_ID C8, SH_MV.TIMES.FISCAL_QUARTER_ID
C9, SH_MV.TIMES.CALENDAR_QUARTER_ID C10, SH_MV.TIMES.FISCAL_MONTH_ID C11, SH_MV.TIMES.CALENDAR_MONTH_ID
C12, SH_MV.TIMES.WEEK_ENDING_DAY_ID C13, SH_MV.SALES.CHANNEL_ID C14, SH_MV.SALES.TIME_ID
C15, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.CHANNELS, SH_MV.PRODUCTS,
SH_MV.TIMES, SH_MV.SALES, SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID AND SH_MV.SALES.TIME_ID
= SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID
= SH_MV.PRODUCTS.PROD_ID AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID
GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID,
SH_MV.CHANNELS.CHANNEL_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_CLASS_ID, SH_MV.PRODUCTS.PROD_TOTAL_ID,
SH_MV.PRODUCTS.PROD_CATEGORY_ID, SH_MV.TIMES.FISCAL_YEAR_ID, SH_MV.TIMES.CALENDAR_YEAR_ID,
SH_MV.TIMES.FISCAL_QUARTER_ID, SH_MV.TIMES.CALENDAR_QUARTER_ID, SH_MV.TIMES.FISCAL_MONTH_ID,
SH_MV.TIMES.CALENDAR_MONTH_ID, SH_MV.TIMES.WEEK_ENDING_DAY_ID, SH_MV.SALES.CHANNEL_ID,
SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420001D"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420001E"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.COUNTRIES.COUNTRY_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.TIMES.FISCAL_YEAR_ID
C3, SH_MV.TIMES.CALENDAR_YEAR_ID C4, SH_MV.TIMES.FISCAL_QUARTER_ID C5, SH_MV.TIMES.CALENDAR_QUARTER_ID
C6, SH_MV.TIMES.FISCAL_MONTH_ID C7, SH_MV.TIMES.CALENDAR_MONTH_ID C8, SH_MV.TIMES.WEEK_ENDING_DAY_ID
C9, SH_MV.SALES.TIME_ID C10, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS,
SH_MV.TIMES, SH_MV.SALES,
SH_MV.COUNTRIES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.TIME_ID = SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID
AND SH_MV.CUSTOMERS.COUNTRY_ID = SH_MV.COUNTRIES.COUNTRY_ID GROUP BY SH_MV.COUNTRIES.COUNTRY_TOTAL_ID,
SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.TIMES.FISCAL_YEAR_ID, SH_MV.TIMES.CALENDAR_YEAR_ID,
SH_MV.TIMES.FISCAL_QUARTER_ID, SH_MV.TIMES.CALENDAR_QUARTER_ID, SH_MV.TIMES.FISCAL_MONTH_ID,
SH_MV.TIMES.CALENDAR_MONTH_ID, SH_MV.TIMES.WEEK_ENDING_DAY_ID, SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420001E"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_0420001F"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.CUSTOMERS.CUST_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.PRODUCTS.PROD_TOTAL_ID
C3, SH_MV.PRODUCTS.PROD_CATEGORY_ID C4, SH_MV.TIMES.FISCAL_YEAR_ID C5, SH_MV.TIMES.CALENDAR_YEAR_ID
C6, SH_MV.TIMES.FISCAL_QUARTER_ID C7, SH_MV.TIMES.CALENDAR_QUARTER_ID C8, SH_MV.TIMES.FISCAL_MONTH_ID
C9, SH_MV.TIMES.CALENDAR_MONTH_ID C10, SH_MV.TIMES.WEEK_ENDING_DAY_ID C11, SH_MV.SALES.TIME_ID
C12, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS, SH_MV.PRODUCTS, SH_MV.TIMES,
SH_MV.SALES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID AND SH_MV.SALES.TIME_ID
= SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID AND SH_MV.SALES.PROD_ID
= SH_MV.PRODUCTS.PROD_ID GROUP BY SH_MV.CUSTOMERS.CUST_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID,
SH_MV.PRODUCTS.PROD_TOTAL_ID, SH_MV.PRODUCTS.PROD_CATEGORY_ID, SH_MV.TIMES.FISCAL_YEAR_ID,
SH_MV.TIMES.CALENDAR_YEAR_ID, SH_MV.TIMES.FISCAL_QUARTER_ID, SH_MV.TIMES.CALENDAR_QUARTER_ID,
SH_MV.TIMES.FISCAL_MONTH_ID, SH_MV.TIMES.CALENDAR_MONTH_ID, SH_MV.TIMES.WEEK_ENDING_DAY_ID,
SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_0420001F"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SH_MV"."MV$$_04200020"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH_MV.CUSTOMERS.CUST_TOTAL_ID C1, SH_MV.PROMOTIONS.PROMO_TOTAL_ID C2, SH_MV.CHANNELS.CHANNEL_TOTAL_ID
C3, SH_MV.CHANNELS.CHANNEL_CLASS_ID C4, SH_MV.TIMES.FISCAL_YEAR_ID C5, SH_MV.TIMES.CALENDAR_YEAR_ID
C6, SH_MV.TIMES.FISCAL_QUARTER_ID C7, SH_MV.TIMES.CALENDAR_QUARTER_ID C8, SH_MV.TIMES.FISCAL_MONTH_ID
C9, SH_MV.TIMES.CALENDAR_MONTH_ID C10, SH_MV.TIMES.WEEK_ENDING_DAY_ID C11, SH_MV.SALES.CHANNEL_ID
C12, SH_MV.SALES.TIME_ID C13, COUNT(*) M1 FROM SH_MV.CUSTOMERS, SH_MV.PROMOTIONS,
SH_MV.CHANNELS, SH_MV.TIMES, SH_MV.SALES WHERE SH_MV.SALES.PROMO_ID = SH_MV.PROMOTIONS.PROMO_ID
AND SH_MV.SALES.CHANNEL_ID = SH_MV.CHANNELS.CHANNEL_ID AND SH_MV.SALES.TIME_ID
= SH_MV.TIMES.TIME_ID AND SH_MV.SALES.CUST_ID = SH_MV.CUSTOMERS.CUST_ID GROUP BY
SH_MV.CUSTOMERS.CUST_TOTAL_ID, SH_MV.PROMOTIONS.PROMO_TOTAL_ID, SH_MV.CHANNELS.CHANNEL_TOTAL_ID,
SH_MV.CHANNELS.CHANNEL_CLASS_ID, SH_MV.TIMES.FISCAL_YEAR_ID, SH_MV.TIMES.CALENDAR_YEAR_ID,
SH_MV.TIMES.FISCAL_QUARTER_ID, SH_MV.TIMES.CALENDAR_QUARTER_ID, SH_MV.TIMES.FISCAL_MONTH_ID,
SH_MV.TIMES.CALENDAR_MONTH_ID, SH_MV.TIMES.WEEK_ENDING_DAY_ID, SH_MV.SALES.CHANNEL_ID,
SH_MV.SALES.TIME_ID;

begin
dbms_stats.gather_table_stats('"SH_MV"','"MV$$_04200020"',NULL,dbms_stats.auto_sample_size);
end;
/

connect sh_test_results/password@ora10g

begin
select to_char(sysdate, 'DD:MM:YYYY HH:MI:SS') into :end_time from dual;
end;
/

connect sh_test_results/password@ora10g

insert into sh_test_results.results (schema_name
,                            run_type_id
,                            start_time
,                            end_time
,                            run_time_secs)
values 			    ('SH_MV'
,                            2
,                            to_date(:start_time,'DD:MM:YYYY HH:MI:SS')
,                            to_date(:end_time,'DD:MM:YYYY HH:MI:SS')
,        	             (to_date(:end_time,'DD:MM:YYYY HH:MI:SS') - to_date(:start_time,'DD:MM:YYYY HH:MI:SS')) * 86400
)
/

commit;


spool off