1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
|
SQL>
SQL> SET ECHO ON
SQL>
SQL> DROP VIEW VUE_SIM_TEST;
View dropped.
SQL>
SQL> CREATE VIEW VUE_SIM_TEST
2 AS
3 SELECT AN, MOIS, C.ID_CLIENT, SUM(MNT_ENGAG) MNT_ENGAG, SUM(MNT_ENC_MOY_ENGAG_M) MNT_ENC_MOY_ENGAG_M,
4 SUM(MNT_ENC_MOY_ENGAG_A) MNT_ENC_MOY_ENGAG_A, SUM(MNT_ENC_MOY_CAUTION_M) MNT_ENC_MOY_CAUTION_M,
5 SUM(MNT_ENC_MOY_CAUTION_A) MNT_ENC_MOY_CAUTION_A, SUM(MNT_PNB_ENGAG_M) MNT_PNB_ENGAG_M,
6 SUM(MNT_PNB_ENGAG_A) MNT_PNB_ENGAG_A, SUM(MNT_COM_ENGAG_M) MNT_COM_ENGAG_M,
7 SUM(MNT_COM_ENGAG_A) MNT_COM_ENGAG_A, SUM(MNT_COM_CAUTION_M) MNT_COM_CAUTION_M,
8 SUM(MNT_COM_CAUTION_A) MNT_COM_CAUTION_A, SUM(MNT_PNB_ENGAG_TOT_M) MNT_PNB_ENGAG_TOT_M,
9 SUM(MNT_PNB_ENGAG_TOT_A) MNT_PNB_ENGAG_TOT_A, SUM(MNT_COM_MVT_M) MNT_COM_MVT_M,
10 SUM(MNT_COM_MVT_A) MNT_COM_MVT_A, SUM(MNT_COM_AUTRE_M) MNT_COM_AUTRE_M,
11 SUM(MNT_COM_AUTRE_A) MNT_COM_AUTRE_A, SUM(MNT_ENC_MOY_DAV_NR_M) MNT_ENC_MOY_DAV_NR_M,
12 SUM(MNT_ENC_MOY_DAV_NR_A) MNT_ENC_MOY_DAV_NR_A, SUM(MNT_PNB_DAV_NR_M) MNT_PNB_DAV_NR_M,
13 SUM(MNT_PNB_DAV_NR_A) MNT_PNB_DAV_NR_A, SUM(MNT_ENC_MOY_DAV_R_M) MNT_ENC_MOY_DAV_R_M,
14 SUM(MNT_ENC_MOY_DAV_R_A) MNT_ENC_MOY_DAV_R_A, SUM(MNT_PNB_DAV_R_M) MNT_PNB_DAV_R_M,
15 SUM(MNT_PNB_DAV_R_A) MNT_PNB_DAV_R_A, SUM(MNT_PNB_FLOT_M) MNT_PNB_FLOT_M,
16 SUM(MNT_PNB_FLOT_A) MNT_PNB_FLOT_A, SUM(MNT_PNB_FLUX_TOT_M) MNT_PNB_FLUX_TOT_M,
17 SUM(MNT_PNB_FLUX_TOT_A) MNT_PNB_FLUX_TOT_A, SUM(MNT_RCA) MNT_RCA
18 FROM TAB_STAT_CLIENT_FLUX F, TAB_COMPTE C
19 WHERE C.ID_COMPTE = F.ID_COMPTE
20 AND AN > TO_CHAR(ADD_MONTHS(SYSDATE,-36),'YYYY')
21 GROUP BY AN, MOIS, C.ID_CLIENT;
View created.
SQL>
SQL> DROP MATERIALIZED VIEW VUE_MAT_TEST;
Materialized view dropped.
SQL>
SQL> CREATE MATERIALIZED VIEW VUE_MAT_TEST
2 TABLESPACE TS_GCI_TAB1
3 BUILD IMMEDIATE
4 REFRESH COMPLETE
5 START WITH SYSDATE NEXT SYSDATE + 1/24
6 WITH PRIMARY KEY
7 AS
8 SELECT AN, MOIS, C.ID_CLIENT, SUM(MNT_ENGAG) MNT_ENGAG, SUM(MNT_ENC_MOY_ENGAG_M) MNT_ENC_MOY_ENGAG_M,
9 SUM(MNT_ENC_MOY_ENGAG_A) MNT_ENC_MOY_ENGAG_A, SUM(MNT_ENC_MOY_CAUTION_M) MNT_ENC_MOY_CAUTION_M,
10 SUM(MNT_ENC_MOY_CAUTION_A) MNT_ENC_MOY_CAUTION_A, SUM(MNT_PNB_ENGAG_M) MNT_PNB_ENGAG_M,
11 SUM(MNT_PNB_ENGAG_A) MNT_PNB_ENGAG_A, SUM(MNT_COM_ENGAG_M) MNT_COM_ENGAG_M,
12 SUM(MNT_COM_ENGAG_A) MNT_COM_ENGAG_A, SUM(MNT_COM_CAUTION_M) MNT_COM_CAUTION_M,
13 SUM(MNT_COM_CAUTION_A) MNT_COM_CAUTION_A, SUM(MNT_PNB_ENGAG_TOT_M) MNT_PNB_ENGAG_TOT_M,
14 SUM(MNT_PNB_ENGAG_TOT_A) MNT_PNB_ENGAG_TOT_A, SUM(MNT_COM_MVT_M) MNT_COM_MVT_M,
15 SUM(MNT_COM_MVT_A) MNT_COM_MVT_A, SUM(MNT_COM_AUTRE_M) MNT_COM_AUTRE_M,
16 SUM(MNT_COM_AUTRE_A) MNT_COM_AUTRE_A, SUM(MNT_ENC_MOY_DAV_NR_M) MNT_ENC_MOY_DAV_NR_M,
17 SUM(MNT_ENC_MOY_DAV_NR_A) MNT_ENC_MOY_DAV_NR_A, SUM(MNT_PNB_DAV_NR_M) MNT_PNB_DAV_NR_M,
18 SUM(MNT_PNB_DAV_NR_A) MNT_PNB_DAV_NR_A, SUM(MNT_ENC_MOY_DAV_R_M) MNT_ENC_MOY_DAV_R_M,
19 SUM(MNT_ENC_MOY_DAV_R_A) MNT_ENC_MOY_DAV_R_A, SUM(MNT_PNB_DAV_R_M) MNT_PNB_DAV_R_M,
20 SUM(MNT_PNB_DAV_R_A) MNT_PNB_DAV_R_A, SUM(MNT_PNB_FLOT_M) MNT_PNB_FLOT_M,
21 SUM(MNT_PNB_FLOT_A) MNT_PNB_FLOT_A, SUM(MNT_PNB_FLUX_TOT_M) MNT_PNB_FLUX_TOT_M,
22 SUM(MNT_PNB_FLUX_TOT_A) MNT_PNB_FLUX_TOT_A, SUM(MNT_RCA) MNT_RCA
23 FROM TAB_STAT_CLIENT_FLUX F, TAB_COMPTE C
24 WHERE C.ID_COMPTE = F.ID_COMPTE
25 AND AN > TO_CHAR(ADD_MONTHS(SYSDATE,-36),'YYYY')
26 GROUP BY AN, MOIS, C.ID_CLIENT;
Materialized view created.
SQL>
SQL> SELECT AN, MOIS, SUM(MNT_PNB_ENGAG_TOT_A)
2 FROM TAB_STAT_CLIENT_FLUX F, TAB_COMPTE C
3 WHERE C.ID_COMPTE = F.ID_COMPTE
4 AND AN = '2006'
5 GROUP BY AN, MOIS
6 ORDER BY 1,2;
AN MO SUM(MNT_PNB_ENGAG_TOT_A)
---- -- ------------------------
2006 01 176829.15
2006 02 341301.02
2006 03 1084345.21
2006 04 1264892
2006 05 1442153.11
2006 06 2231253.43
2006 07 2424360.56
2006 08 2621464.4
2006 09 3362098.42
9 rows selected.
SQL>
SQL> SELECT AN, MOIS, SUM(MNT_PNB_ENGAG_TOT_A)
2 FROM VUE_SIM_TEST
3 WHERE AN = '2006'
4 GROUP BY AN, MOIS
5 ORDER BY 1,2;
AN MO SUM(MNT_PNB_ENGAG_TOT_A)
---- -- ------------------------
2006 01 176829.15
2006 02 341301.02
2006 03 1084345.21
2006 04 1264892
2006 05 1442153.11
2006 06 2231253.43
2006 07 2424360.56
2006 08 2621464.4
2006 09 3362098.42
9 rows selected.
SQL>
SQL> SELECT AN, MOIS, SUM(MNT_PNB_ENGAG_TOT_A)
2 FROM VUE_MAT_TEST
3 WHERE AN = '2006'
4 GROUP BY AN, MOIS
5 ORDER BY 1,2;
AN MO SUM(MNT_PNB_ENGAG_TOT_A)
---- -- ------------------------
2006 01 176829.15
2006 02 341301.02
2006 03 1084345.21
2006 04 1264892
2006 05 1442153.11
2006 06 2231253.43
2006 07 2424360.56
2006 08 2621464.4
2006 09 3298491.27
9 rows selected.
SQL>
SQL> SPOOL OFF |
Vues matérialisées : problème
Répondre avec citation
Partager