
DROP MATERIALIZED VIEW LOG ON EX_DATA01 ;
DROP MATERIALIZED VIEW LOG ON EX_DAY ;


DROP MATERIALIZED VIEW WVIEW0011_YYYY;
DROP TABLE WVIEW0011_YYYY PURGE;

CREATE TABLE WVIEW0011_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_DATA01 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 WVIEW0011_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_WVIEW0011_YYYY_1
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYY_2
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYY_3
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYY_4
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYY_5
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYY_6
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYY_7
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYY_8
ON WVIEW0011_YYYY
(
VPERIOD
,VKEY8
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0011_HALFYEAR;
DROP TABLE WVIEW0011_HALFYEAR PURGE;

CREATE TABLE WVIEW0011_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_DATA01 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 WVIEW0011_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_WVIEW0011_HALFYEAR_1
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_HALFYEAR_2
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_HALFYEAR_3
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_HALFYEAR_4
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_HALFYEAR_5
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_HALFYEAR_6
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_HALFYEAR_7
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_HALFYEAR_8
ON WVIEW0011_HALFYEAR
(
VPERIOD
,VKEY8
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0011_TERM;
DROP TABLE WVIEW0011_TERM PURGE;

CREATE TABLE WVIEW0011_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_DATA01 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 WVIEW0011_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_WVIEW0011_TERM_1
ON WVIEW0011_TERM
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_TERM_2
ON WVIEW0011_TERM
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_TERM_3
ON WVIEW0011_TERM
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_TERM_4
ON WVIEW0011_TERM
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_TERM_5
ON WVIEW0011_TERM
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_TERM_6
ON WVIEW0011_TERM
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_TERM_7
ON WVIEW0011_TERM
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_TERM_8
ON WVIEW0011_TERM
(
VPERIOD
,VKEY8
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0011_YYYYMM;
DROP TABLE WVIEW0011_YYYYMM PURGE;

CREATE TABLE WVIEW0011_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_DATA01 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 WVIEW0011_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_WVIEW0011_YYYYMM_1
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYYMM_2
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYYMM_3
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYYMM_4
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYYMM_5
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYYMM_6
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYYMM_7
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0011_YYYYMM_8
ON WVIEW0011_YYYYMM
(
VPERIOD
,VKEY8
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0012_YYYY;
DROP TABLE WVIEW0012_YYYY PURGE;

CREATE TABLE WVIEW0012_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_DATA01 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE2
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 WVIEW0012_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_WVIEW0012_YYYY_1
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYY_2
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYY_3
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYY_4
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYY_5
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYY_6
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYY_7
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYY_8
ON WVIEW0012_YYYY
(
VPERIOD
,VKEY8
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0012_HALFYEAR;
DROP TABLE WVIEW0012_HALFYEAR PURGE;

CREATE TABLE WVIEW0012_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_DATA01 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE2
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 WVIEW0012_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_WVIEW0012_HALFYEAR_1
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_HALFYEAR_2
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_HALFYEAR_3
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_HALFYEAR_4
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_HALFYEAR_5
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_HALFYEAR_6
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_HALFYEAR_7
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_HALFYEAR_8
ON WVIEW0012_HALFYEAR
(
VPERIOD
,VKEY8
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0012_TERM;
DROP TABLE WVIEW0012_TERM PURGE;

CREATE TABLE WVIEW0012_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_DATA01 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE2
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 WVIEW0012_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_WVIEW0012_TERM_1
ON WVIEW0012_TERM
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_TERM_2
ON WVIEW0012_TERM
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_TERM_3
ON WVIEW0012_TERM
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_TERM_4
ON WVIEW0012_TERM
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_TERM_5
ON WVIEW0012_TERM
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_TERM_6
ON WVIEW0012_TERM
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_TERM_7
ON WVIEW0012_TERM
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_TERM_8
ON WVIEW0012_TERM
(
VPERIOD
,VKEY8
)
NOLOGGING
;


COMMIT;


DROP MATERIALIZED VIEW WVIEW0012_YYYYMM;
DROP TABLE WVIEW0012_YYYYMM PURGE;

CREATE TABLE WVIEW0012_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_DATA01 A
INNER JOIN EX_DAY B ON B.EX_DATE = A.DATE2
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 WVIEW0012_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_WVIEW0012_YYYYMM_1
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY1
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYYMM_2
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY2
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYYMM_3
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY3
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYYMM_4
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY4
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYYMM_5
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY5
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYYMM_6
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY6
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYYMM_7
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY7
)
NOLOGGING
;

CREATE INDEX IDX_WVIEW0012_YYYYMM_8
ON WVIEW0012_YYYYMM
(
VPERIOD
,VKEY8
)
NOLOGGING
;

COMMIT;


EXIT
