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

spool c:\agg_test_files\init_agg_SH_ODM.log

prompt Initial aggregation of the SH_ODM data

connect SH_ODM/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_ODM"."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_ODM"."COUNTRIES"
WITH ROWID, SEQUENCE("COUNTRY_ID","COUNTRY_SUBREGION_ID","COUNTRY_REGION_ID","COUNTRY_TOTAL_ID")
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
"SH_ODM"."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_ODM"."PROMOTIONS"
WITH ROWID, SEQUENCE("PROMO_ID","PROMO_TOTAL_ID")
INCLUDING NEW VALUES;

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

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

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

CREATE MATERIALIZED VIEW "SH_ODM"."MV$SALES_HMV"
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS 
SELECT  CU.CUST_TOTAL_ID C1
,       CU.CUST_STATE_PROVINCE_ID C2
,       CU.CUST_CITY_ID C3
,	CU.COUNTRY_ID C4
,	CT.COUNTRY_TOTAL_ID C5
,	 CT.COUNTRY_REGION_ID C6
, 	 CT.COUNTRY_SUBREGION_ID C7
,       S.CUST_ID C8
, 	PM.PROMO_TOTAL_ID C9
,       PM.PROMO_CATEGORY_ID C10
,       PM.PROMO_SUBCATEGORY_ID C11
,       S.PROMO_ID C12
, 	CH.CHANNEL_TOTAL_ID C13
, 	CH.CHANNEL_CLASS_ID C14
, 	PD.PROD_CATEGORY_ID C15
, 	PD.PROD_SUBCATEGORY_ID C16
, 	S.PROD_ID C17
,       T.CALENDAR_YEAR_ID C18
,       T.CALENDAR_QUARTER_ID C19
,       T.CALENDAR_MONTH_ID C20
,       S.TIME_ID C21
, 	COUNT(*) M1
,       SUM(S.QUANTITY_SOLD) QUANTITY_SOLD
,       SUM(S.AMOUNT_SOLD) AMOUNT_SOLD 
,       COUNT(S.QUANTITY_SOLD) C_QUANTITY_SOLD
,       COUNT(S.AMOUNT_SOLD) C_AMOUNT_SOLD
FROM 	SH_ODM.CUSTOMERS CU
, 	SH_ODM.PROMOTIONS PM
, 	SH_ODM.CHANNELS CH
, 	SH_ODM.PRODUCTS PD
,       SH_ODM.TIMES T
, 	SH_ODM.SALES S
,       SH_ODM.COUNTRIES CT
WHERE 	S.PROMO_ID = PM.PROMO_ID 
AND 	S.CHANNEL_ID = CH.CHANNEL_ID 
AND 	S.CUST_ID = CU.CUST_ID 
AND	S.PROD_ID = PD.PROD_ID 
AND     S.TIME_ID = T.TIME_ID
AND	CU.COUNTRY_ID = CT.COUNTRY_ID
GROUP BY
 GROUPING SETS
(
(	CU.CUST_TOTAL_ID
,	 PM.PROMO_TOTAL_ID
,	 CH.CHANNEL_TOTAL_ID
,	 CH.CHANNEL_CLASS_ID
,	 PD.PROD_TOTAL_ID
, 	 PD.PROD_CATEGORY_ID
, 	 PD.PROD_SUBCATEGORY_ID
,	 S.CHANNEL_ID
, 	 S.PROD_ID)
,
(	 CT.COUNTRY_TOTAL_ID
,	 CT.COUNTRY_REGION_ID
, 	 CT.COUNTRY_SUBREGION_ID
, 	 CU.COUNTRY_ID
,	 PM.PROMO_TOTAL_ID
, 	 CH.CHANNEL_TOTAL_ID
, 	 CH.CHANNEL_CLASS_ID
, 	 PD.PROD_TOTAL_ID
,	 PD.PROD_CATEGORY_ID
, 	 S.CHANNEL_ID)
,
(	 CT.COUNTRY_TOTAL_ID
,	PM.PROMO_TOTAL_ID
, 	CH.CHANNEL_TOTAL_ID
, 	CH.CHANNEL_CLASS_ID
,	PD.PROD_TOTAL_ID
, 	PD.PROD_CATEGORY_ID
, 	S.CHANNEL_ID)
,
( 	CT.COUNTRY_TOTAL_ID
, 	CT.COUNTRY_REGION_ID
, 	CT.COUNTRY_SUBREGION_ID
, 	CU.COUNTRY_ID
,	PM.PROMO_TOTAL_ID
, 	CH.CHANNEL_TOTAL_ID
, 	CH.CHANNEL_CLASS_ID
,	PD.PROD_TOTAL_ID
, 	PD.PROD_CATEGORY_ID
, 	PD.PROD_SUBCATEGORY_ID
,	S.CHANNEL_ID, S.PROD_ID)
,
( 	CU.CUST_TOTAL_ID
, 	PM.PROMO_TOTAL_ID
, 	PD.PROD_TOTAL_ID
,	PD.PROD_CATEGORY_ID)
,
(	CT.COUNTRY_TOTAL_ID
,	PM.PROMO_TOTAL_ID
, 	PD.PROD_TOTAL_ID
, 	PD.PROD_CATEGORY_ID
,	PD.PROD_SUBCATEGORY_ID
, 	S.PROD_ID)
,
(	CU.CUST_TOTAL_ID
, 	PM.PROMO_TOTAL_ID
, 	CH.CHANNEL_TOTAL_ID
, 	CH.CHANNEL_CLASS_ID
,	S.CHANNEL_ID)
,
(	CU.CUST_TOTAL_ID
, 	 PM.PROMO_TOTAL_ID
, 	 CH.CHANNEL_TOTAL_ID
,	 CH.CHANNEL_CLASS_ID
, 	 PD.PROD_TOTAL_ID
, 	 PD.PROD_CATEGORY_ID
,	 T.FISCAL_YEAR_ID
, 	 T.CALENDAR_YEAR_ID
, 	 T.FISCAL_QUARTER_ID
, 	 T.CALENDAR_QUARTER_ID
, 	 T.FISCAL_MONTH_ID
, 	 T.CALENDAR_MONTH_ID
,	 T.WEEK_ENDING_DAY_ID
, 	 S.CHANNEL_ID
, 	 S.TIME_ID)
,
( 	CT.COUNTRY_TOTAL_ID
,	PM.PROMO_TOTAL_ID)
,
(	CT.COUNTRY_TOTAL_ID
, 	CT.COUNTRY_REGION_ID
, 	CT.COUNTRY_SUBREGION_ID
, 	CU.COUNTRY_ID
,	PM.PROMO_TOTAL_ID
, 	PD.PROD_TOTAL_ID
, 	PD.PROD_CATEGORY_ID)
,
(	CT.COUNTRY_TOTAL_ID
,	PM.PROMO_TOTAL_ID
, 	CH.CHANNEL_TOTAL_ID
, 	CH.CHANNEL_CLASS_ID
,	PD.PROD_TOTAL_ID
, 	PD.PROD_CATEGORY_ID
, 	PD.PROD_SUBCATEGORY_ID
,	S.CHANNEL_ID
, 	S.PROD_ID)
,
( 	CT.COUNTRY_TOTAL_ID
, 	CT.COUNTRY_REGION_ID
,	CT.COUNTRY_SUBREGION_ID
, 	CU.CUST_TOTAL_ID
, 	CU.COUNTRY_ID
,	CU.CUST_STATE_PROVINCE_ID
, 	CU.CUST_CITY_ID
, 	PM.PROMO_TOTAL_ID,
	S.CUST_ID)
,
(	CU.CUST_TOTAL_ID
, 	PM.PROMO_TOTAL_ID
, 	CH.CHANNEL_TOTAL_ID
,	CH.CHANNEL_CLASS_ID
,	PD.PROD_TOTAL_ID
, 	PD.PROD_CATEGORY_ID,
	S.CHANNEL_ID)
,
(	CT.COUNTRY_TOTAL_ID
, 	PM.PROMO_TOTAL_ID
,	PD.PROD_TOTAL_ID
, 	PD.PROD_CATEGORY_ID
, 	T.FISCAL_YEAR_ID
,	T.CALENDAR_YEAR_ID
, 	T.FISCAL_QUARTER_ID
, 	T.CALENDAR_QUARTER_ID
,	T.FISCAL_MONTH_ID
, 	T.CALENDAR_MONTH_ID
, 	T.WEEK_ENDING_DAY_ID
,	S.TIME_ID)
,
(	CT.COUNTRY_TOTAL_ID,
CT.COUNTRY_REGION_ID, CT.COUNTRY_SUBREGION_ID, CU.COUNTRY_ID,
PM.PROMO_TOTAL_ID, PD.PROD_TOTAL_ID, PD.PROD_CATEGORY_ID,
PD.PROD_SUBCATEGORY_ID, S.PROD_ID)
,
(	CT.COUNTRY_TOTAL_ID,
CT.COUNTRY_REGION_ID, CT.COUNTRY_SUBREGION_ID, CU.COUNTRY_ID,
PM.PROMO_TOTAL_ID, CH.CHANNEL_TOTAL_ID, CH.CHANNEL_CLASS_ID,
S.CHANNEL_ID)
,
(	CT.COUNTRY_TOTAL_ID,
CT.COUNTRY_REGION_ID, CT.COUNTRY_SUBREGION_ID, CU.COUNTRY_ID,
PM.PROMO_TOTAL_ID)
,
(	CU.CUST_TOTAL_ID, PM.PROMO_TOTAL_ID, PD.PROD_TOTAL_ID,
PD.PROD_CATEGORY_ID, PD.PROD_SUBCATEGORY_ID, S.PROD_ID)
,
(	CU.CUST_TOTAL_ID, PM.PROMO_TOTAL_ID)
,
(	CT.COUNTRY_TOTAL_ID,
PM.PROMO_TOTAL_ID, PD.PROD_TOTAL_ID, PD.PROD_CATEGORY_ID)
,
(	CT.COUNTRY_TOTAL_ID,
PM.PROMO_TOTAL_ID, CH.CHANNEL_TOTAL_ID, CH.CHANNEL_CLASS_ID,
S.CHANNEL_ID)
,
(	CT.COUNTRY_TOTAL_ID,
PM.PROMO_TOTAL_ID, CH.CHANNEL_TOTAL_ID, CH.CHANNEL_CLASS_ID,
T.FISCAL_YEAR_ID, T.CALENDAR_YEAR_ID, T.FISCAL_QUARTER_ID,
T.CALENDAR_QUARTER_ID, T.FISCAL_MONTH_ID, T.CALENDAR_MONTH_ID,
T.WEEK_ENDING_DAY_ID, S.CHANNEL_ID, S.TIME_ID)
,
(	CU.CUST_TOTAL_ID, PM.PROMO_TOTAL_ID, T.FISCAL_YEAR_ID,
T.CALENDAR_YEAR_ID, T.FISCAL_QUARTER_ID, T.CALENDAR_QUARTER_ID,
T.FISCAL_MONTH_ID, T.CALENDAR_MONTH_ID, T.WEEK_ENDING_DAY_ID,
S.TIME_ID)
,
( 	CT.COUNTRY_TOTAL_ID,
CT.COUNTRY_REGION_ID, CT.COUNTRY_SUBREGION_ID, CU.COUNTRY_ID,
PM.PROMO_TOTAL_ID, T.FISCAL_YEAR_ID, T.CALENDAR_YEAR_ID,
T.FISCAL_QUARTER_ID, T.CALENDAR_QUARTER_ID, T.FISCAL_MONTH_ID,
T.CALENDAR_MONTH_ID, T.WEEK_ENDING_DAY_ID, S.TIME_ID)
,
(CT.COUNTRY_TOTAL_ID, PM.PROMO_TOTAL_ID,
CH.CHANNEL_TOTAL_ID, CH.CHANNEL_CLASS_ID, PD.PROD_TOTAL_ID,
PD.PROD_CATEGORY_ID, T.FISCAL_YEAR_ID, T.CALENDAR_YEAR_ID,
T.FISCAL_QUARTER_ID, T.CALENDAR_QUARTER_ID, T.FISCAL_MONTH_ID,
T.CALENDAR_MONTH_ID, T.WEEK_ENDING_DAY_ID, S.CHANNEL_ID,
S.TIME_ID)
,
(CT.COUNTRY_TOTAL_ID,
PM.PROMO_TOTAL_ID, T.FISCAL_YEAR_ID, T.CALENDAR_YEAR_ID,
T.FISCAL_QUARTER_ID, T.CALENDAR_QUARTER_ID, T.FISCAL_MONTH_ID,
T.CALENDAR_MONTH_ID, T.WEEK_ENDING_DAY_ID, S.PROMO_ID, S.TIME_ID, PM.PROMO_CATEGORY_ID)
,
(CU.CUST_TOTAL_ID, PM.PROMO_SUBCATEGORY_ID,
PD.PROD_TOTAL_ID, PD.PROD_CATEGORY_ID, T.FISCAL_YEAR_ID,
T.CALENDAR_YEAR_ID, T.FISCAL_QUARTER_ID, T.CALENDAR_QUARTER_ID,
T.FISCAL_MONTH_ID, T.CALENDAR_MONTH_ID, T.WEEK_ENDING_DAY_ID,
S.TIME_ID)
,
(CU.CUST_TOTAL_ID, PM.PROMO_CATEGORY_ID, CH.CHANNEL_TOTAL_ID,
CH.CHANNEL_CLASS_ID, T.FISCAL_YEAR_ID, T.CALENDAR_YEAR_ID,
T.FISCAL_QUARTER_ID, T.CALENDAR_QUARTER_ID, T.FISCAL_MONTH_ID,
T.CALENDAR_MONTH_ID, T.WEEK_ENDING_DAY_ID, S.CHANNEL_ID,
S.TIME_ID)
);

begin
dbms_stats.gather_table_stats('"SH_ODM"','"MV$SALES_HMV"',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_ODM'
,                            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