
DROP MATERIALIZED VIEW LOG ON EX_DATA6 ;
DROP MATERIALIZED VIEW LOG ON EX_DAY ;


DROP MATERIALIZED VIEW WVIEW0061_YYYY;
DROP TABLE WVIEW0061_YYYY PURGE;

CREATE TABLE WVIEW0061_YYYY
NOLOGGING
AS
SELECT
B.EX_YYYY "VPERIOD"
,A.KEY1 "VKEY1"
,A.KEY2 "VKEY2"
,A.KEY3 "VKEY3"
,A.KEY4 "VKEY4"
,A.KEY5 "VKEY5"
,A.KEY6 "VKEY6"
,A.KEY7 "VKEY7"
,A.KEY8 "VKEY8"
,A.KEY9 "VKEY9"
,A.KEY10 "VKEY10"
,A.KEY11 "VKEY11"
,A.KEY12 "VKEY12"
,A.KEY13 "VKEY13"
,A.KEY14 "VKEY14"
,A.KEY15 "VKEY15"
,A.KEY16 "VKEY16"
,A.KEY17 "VKEY17"
,A.KEY18 "VKEY18"
,A.KEY19 "VKEY19"
,A.KEY20 "VKEY20"
,A.KEY21 "VKEY21"
,A.KEY22 "VKEY22"
,A.KEY23 "VKEY23"
,A.KEY24 "VKEY24"
,A.KEY25 "VKEY25"
,A.KEY26 "VKEY26"
,A.KEY27 "VKEY27"
,A.KEY28 "VKEY28"
,A.KEY29 "VKEY29"
,A.KEY30 "VKEY30"
,SUM(A.D1) "VD1"
,SUM(A.D2) "VD2"
,SUM(A.D3) "VD3"
,SUM(A.D4) "VD4"
,SUM(A.D5) "VD5"
,SUM(A.D6) "VD6"
,SUM(A.D7) "VD7"
,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9"
,SUM(A.D10) "VD10"
,SUM(A.D11) "VD11"
,SUM(A.D12) "VD12"
,SUM(A.D13) "VD13"
,SUM(A.D14) "VD14"
,SUM(A.D15) "VD15"
,SUM(A.D16) "VD16"
,SUM(A.D17) "VD17"
,SUM(A.D18) "VD18"
,SUM(A.D19) "VD19"
,SUM(A.D20) "VD20"
,SUM(A.D21) "VD21"
,SUM(A.D22) "VD22"
,SUM(A.D23) "VD23"
,SUM(A.D24) "VD24"
,SUM(A.D25) "VD25"
,SUM(A.D26) "VD26"
,SUM(A.D27) "VD27"
,SUM(A.D28) "VD28"
,SUM(A.D29) "VD29"
,SUM(A.D30) "VD30"
FROM EX_DATA6 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE1
GROUP BY
B.EX_YYYY
,A.KEY1
,A.KEY2
,A.KEY3
,A.KEY4
,A.KEY5
,A.KEY6
,A.KEY7
,A.KEY8
,A.KEY9
,A.KEY10
,A.KEY11
,A.KEY12
,A.KEY13
,A.KEY14
,A.KEY15
,A.KEY16
,A.KEY17
,A.KEY18
,A.KEY19
,A.KEY20
,A.KEY21
,A.KEY22
,A.KEY23
,A.KEY24
,A.KEY25
,A.KEY26
,A.KEY27
,A.KEY28
,A.KEY29
,A.KEY30
;
ALTER TABLE WVIEW0061_YYYY ADD PRIMARY KEY
(
VPERIOD
, VKEY1
, VKEY2
, VKEY3
, VKEY4
, VKEY5
, VKEY6
, VKEY7
, VKEY8
, VKEY9
, VKEY10
, VKEY11
, VKEY12
, VKEY13
, VKEY14
, VKEY15
, VKEY16
, VKEY17
, VKEY18
, VKEY19
, VKEY20
, VKEY21
, VKEY22
, VKEY23
, VKEY24
, VKEY25
, VKEY26
, VKEY27
, VKEY28
, VKEY29
, VKEY30
)
NOLOGGING
;


CREATE INDEX IDX_WVIEW0061_YYYY_1
ON WVIEW0061_YYYY
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_YYYY_2
ON WVIEW0061_YYYY
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_YYYY_3
ON WVIEW0061_YYYY
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_YYYY_4
ON WVIEW0061_YYYY
(
VPERIOD
,VKEY4
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0061_HALFYEAR;
DROP TABLE WVIEW0061_HALFYEAR PURGE;

CREATE TABLE WVIEW0061_HALFYEAR
NOLOGGING
AS
SELECT
B.EX_HALFYEAR "VPERIOD"
,A.KEY1 "VKEY1"
,A.KEY2 "VKEY2"
,A.KEY3 "VKEY3"
,A.KEY4 "VKEY4"
,A.KEY5 "VKEY5"
,A.KEY6 "VKEY6"
,A.KEY7 "VKEY7"
,A.KEY8 "VKEY8"
,A.KEY9 "VKEY9"
,A.KEY10 "VKEY10"
,A.KEY11 "VKEY11"
,A.KEY12 "VKEY12"
,A.KEY13 "VKEY13"
,A.KEY14 "VKEY14"
,A.KEY15 "VKEY15"
,A.KEY16 "VKEY16"
,A.KEY17 "VKEY17"
,A.KEY18 "VKEY18"
,A.KEY19 "VKEY19"
,A.KEY20 "VKEY20"
,A.KEY21 "VKEY21"
,A.KEY22 "VKEY22"
,A.KEY23 "VKEY23"
,A.KEY24 "VKEY24"
,A.KEY25 "VKEY25"
,A.KEY26 "VKEY26"
,A.KEY27 "VKEY27"
,A.KEY28 "VKEY28"
,A.KEY29 "VKEY29"
,A.KEY30 "VKEY30"
,SUM(A.D1) "VD1"
,SUM(A.D2) "VD2"
,SUM(A.D3) "VD3"
,SUM(A.D4) "VD4"
,SUM(A.D5) "VD5"
,SUM(A.D6) "VD6"
,SUM(A.D7) "VD7"
,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9"
,SUM(A.D10) "VD10"
,SUM(A.D11) "VD11"
,SUM(A.D12) "VD12"
,SUM(A.D13) "VD13"
,SUM(A.D14) "VD14"
,SUM(A.D15) "VD15"
,SUM(A.D16) "VD16"
,SUM(A.D17) "VD17"
,SUM(A.D18) "VD18"
,SUM(A.D19) "VD19"
,SUM(A.D20) "VD20"
,SUM(A.D21) "VD21"
,SUM(A.D22) "VD22"
,SUM(A.D23) "VD23"
,SUM(A.D24) "VD24"
,SUM(A.D25) "VD25"
,SUM(A.D26) "VD26"
,SUM(A.D27) "VD27"
,SUM(A.D28) "VD28"
,SUM(A.D29) "VD29"
,SUM(A.D30) "VD30"
FROM EX_DATA6 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE1
GROUP BY
B.EX_HALFYEAR
,A.KEY1
,A.KEY2
,A.KEY3
,A.KEY4
,A.KEY5
,A.KEY6
,A.KEY7
,A.KEY8
,A.KEY9
,A.KEY10
,A.KEY11
,A.KEY12
,A.KEY13
,A.KEY14
,A.KEY15
,A.KEY16
,A.KEY17
,A.KEY18
,A.KEY19
,A.KEY20
,A.KEY21
,A.KEY22
,A.KEY23
,A.KEY24
,A.KEY25
,A.KEY26
,A.KEY27
,A.KEY28
,A.KEY29
,A.KEY30
;
ALTER TABLE WVIEW0061_HALFYEAR ADD PRIMARY KEY
(
VPERIOD
, VKEY1
, VKEY2
, VKEY3
, VKEY4
, VKEY5
, VKEY6
, VKEY7
, VKEY8
, VKEY9
, VKEY10
, VKEY11
, VKEY12
, VKEY13
, VKEY14
, VKEY15
, VKEY16
, VKEY17
, VKEY18
, VKEY19
, VKEY20
, VKEY21
, VKEY22
, VKEY23
, VKEY24
, VKEY25
, VKEY26
, VKEY27
, VKEY28
, VKEY29
, VKEY30
)
NOLOGGING
;



CREATE INDEX IDX_WVIEW0061_HALFYEAR_1
ON WVIEW0061_HALFYEAR
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_HALFYEAR_2
ON WVIEW0061_HALFYEAR
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_HALFYEAR_3
ON WVIEW0061_HALFYEAR
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_HALFYEAR_4
ON WVIEW0061_HALFYEAR
(
VPERIOD
,VKEY4
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0061_TERM;
DROP TABLE WVIEW0061_TERM PURGE;

CREATE TABLE WVIEW0061_TERM
NOLOGGING
AS
SELECT
B.EX_TERM "VPERIOD"
,A.KEY1 "VKEY1"
,A.KEY2 "VKEY2"
,A.KEY3 "VKEY3"
,A.KEY4 "VKEY4"
,A.KEY5 "VKEY5"
,A.KEY6 "VKEY6"
,A.KEY7 "VKEY7"
,A.KEY8 "VKEY8"
,A.KEY9 "VKEY9"
,A.KEY10 "VKEY10"
,A.KEY11 "VKEY11"
,A.KEY12 "VKEY12"
,A.KEY13 "VKEY13"
,A.KEY14 "VKEY14"
,A.KEY15 "VKEY15"
,A.KEY16 "VKEY16"
,A.KEY17 "VKEY17"
,A.KEY18 "VKEY18"
,A.KEY19 "VKEY19"
,A.KEY20 "VKEY20"
,A.KEY21 "VKEY21"
,A.KEY22 "VKEY22"
,A.KEY23 "VKEY23"
,A.KEY24 "VKEY24"
,A.KEY25 "VKEY25"
,A.KEY26 "VKEY26"
,A.KEY27 "VKEY27"
,A.KEY28 "VKEY28"
,A.KEY29 "VKEY29"
,A.KEY30 "VKEY30"
,SUM(A.D1) "VD1"
,SUM(A.D2) "VD2"
,SUM(A.D3) "VD3"
,SUM(A.D4) "VD4"
,SUM(A.D5) "VD5"
,SUM(A.D6) "VD6"
,SUM(A.D7) "VD7"
,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9"
,SUM(A.D10) "VD10"
,SUM(A.D11) "VD11"
,SUM(A.D12) "VD12"
,SUM(A.D13) "VD13"
,SUM(A.D14) "VD14"
,SUM(A.D15) "VD15"
,SUM(A.D16) "VD16"
,SUM(A.D17) "VD17"
,SUM(A.D18) "VD18"
,SUM(A.D19) "VD19"
,SUM(A.D20) "VD20"
,SUM(A.D21) "VD21"
,SUM(A.D22) "VD22"
,SUM(A.D23) "VD23"
,SUM(A.D24) "VD24"
,SUM(A.D25) "VD25"
,SUM(A.D26) "VD26"
,SUM(A.D27) "VD27"
,SUM(A.D28) "VD28"
,SUM(A.D29) "VD29"
,SUM(A.D30) "VD30"
FROM EX_DATA6 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE1
GROUP BY
B.EX_TERM
,A.KEY1
,A.KEY2
,A.KEY3
,A.KEY4
,A.KEY5
,A.KEY6
,A.KEY7
,A.KEY8
,A.KEY9
,A.KEY10
,A.KEY11
,A.KEY12
,A.KEY13
,A.KEY14
,A.KEY15
,A.KEY16
,A.KEY17
,A.KEY18
,A.KEY19
,A.KEY20
,A.KEY21
,A.KEY22
,A.KEY23
,A.KEY24
,A.KEY25
,A.KEY26
,A.KEY27
,A.KEY28
,A.KEY29
,A.KEY30
;
ALTER TABLE WVIEW0061_TERM ADD PRIMARY KEY
(
VPERIOD
, VKEY1
, VKEY2
, VKEY3
, VKEY4
, VKEY5
, VKEY6
, VKEY7
, VKEY8
, VKEY9
, VKEY10
, VKEY11
, VKEY12
, VKEY13
, VKEY14
, VKEY15
, VKEY16
, VKEY17
, VKEY18
, VKEY19
, VKEY20
, VKEY21
, VKEY22
, VKEY23
, VKEY24
, VKEY25
, VKEY26
, VKEY27
, VKEY28
, VKEY29
, VKEY30
)
NOLOGGING
;



CREATE INDEX IDX_WVIEW0061_TERM_1
ON WVIEW0061_TERM
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_TERM_2
ON WVIEW0061_TERM
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_TERM_3
ON WVIEW0061_TERM
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_TERM_4
ON WVIEW0061_TERM
(
VPERIOD
,VKEY4
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0061_YYYYMM;
DROP TABLE WVIEW0061_YYYYMM PURGE;

CREATE TABLE WVIEW0061_YYYYMM
NOLOGGING
AS
SELECT
B.EX_YYYYMM "VPERIOD"
,A.KEY1 "VKEY1"
,A.KEY2 "VKEY2"
,A.KEY3 "VKEY3"
,A.KEY4 "VKEY4"
,A.KEY5 "VKEY5"
,A.KEY6 "VKEY6"
,A.KEY7 "VKEY7"
,A.KEY8 "VKEY8"
,A.KEY9 "VKEY9"
,A.KEY10 "VKEY10"
,A.KEY11 "VKEY11"
,A.KEY12 "VKEY12"
,A.KEY13 "VKEY13"
,A.KEY14 "VKEY14"
,A.KEY15 "VKEY15"
,A.KEY16 "VKEY16"
,A.KEY17 "VKEY17"
,A.KEY18 "VKEY18"
,A.KEY19 "VKEY19"
,A.KEY20 "VKEY20"
,A.KEY21 "VKEY21"
,A.KEY22 "VKEY22"
,A.KEY23 "VKEY23"
,A.KEY24 "VKEY24"
,A.KEY25 "VKEY25"
,A.KEY26 "VKEY26"
,A.KEY27 "VKEY27"
,A.KEY28 "VKEY28"
,A.KEY29 "VKEY29"
,A.KEY30 "VKEY30"
,SUM(A.D1) "VD1"
,SUM(A.D2) "VD2"
,SUM(A.D3) "VD3"
,SUM(A.D4) "VD4"
,SUM(A.D5) "VD5"
,SUM(A.D6) "VD6"
,SUM(A.D7) "VD7"
,SUM(A.D8) "VD8"
,SUM(A.D9) "VD9"
,SUM(A.D10) "VD10"
,SUM(A.D11) "VD11"
,SUM(A.D12) "VD12"
,SUM(A.D13) "VD13"
,SUM(A.D14) "VD14"
,SUM(A.D15) "VD15"
,SUM(A.D16) "VD16"
,SUM(A.D17) "VD17"
,SUM(A.D18) "VD18"
,SUM(A.D19) "VD19"
,SUM(A.D20) "VD20"
,SUM(A.D21) "VD21"
,SUM(A.D22) "VD22"
,SUM(A.D23) "VD23"
,SUM(A.D24) "VD24"
,SUM(A.D25) "VD25"
,SUM(A.D26) "VD26"
,SUM(A.D27) "VD27"
,SUM(A.D28) "VD28"
,SUM(A.D29) "VD29"
,SUM(A.D30) "VD30"
FROM EX_DATA6 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE1
GROUP BY
B.EX_YYYYMM
,A.KEY1
,A.KEY2
,A.KEY3
,A.KEY4
,A.KEY5
,A.KEY6
,A.KEY7
,A.KEY8
,A.KEY9
,A.KEY10
,A.KEY11
,A.KEY12
,A.KEY13
,A.KEY14
,A.KEY15
,A.KEY16
,A.KEY17
,A.KEY18
,A.KEY19
,A.KEY20
,A.KEY21
,A.KEY22
,A.KEY23
,A.KEY24
,A.KEY25
,A.KEY26
,A.KEY27
,A.KEY28
,A.KEY29
,A.KEY30
;
ALTER TABLE WVIEW0061_YYYYMM ADD PRIMARY KEY
(
VPERIOD
, VKEY1
, VKEY2
, VKEY3
, VKEY4
, VKEY5
, VKEY6
, VKEY7
, VKEY8
, VKEY9
, VKEY10
, VKEY11
, VKEY12
, VKEY13
, VKEY14
, VKEY15
, VKEY16
, VKEY17
, VKEY18
, VKEY19
, VKEY20
, VKEY21
, VKEY22
, VKEY23
, VKEY24
, VKEY25
, VKEY26
, VKEY27
, VKEY28
, VKEY29
, VKEY30
)
NOLOGGING
;



CREATE INDEX IDX_WVIEW0061_YYYYMM_1
ON WVIEW0061_YYYYMM
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_YYYYMM_2
ON WVIEW0061_YYYYMM
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_YYYYMM_3
ON WVIEW0061_YYYYMM
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0061_YYYYMM_4
ON WVIEW0061_YYYYMM
(
VPERIOD
,VKEY4
)
NOLOGGING
;

COMMIT;


EXIT
