Mise a jour d'une table aggregée

**Karibou** · 26/10/2009, 20h26

Bonjour,

Ca fait plusieurs jours que je me casse la tête sur le probleme suivant

:

J'ai 4 tables:
1. une grosse table de fait qui contient des données numeriques par site de production (envion 100 000 differents).
Cette table, contient comme champs: l'id du site, la date des valeurs, la date d'insertion, et un multitudes de mesures (quantité produite, prix, etc.)
2. une deuxieme table qui contient les mêmes champs mais aggrégés a la semaine et non plus au jour.
3. une table de status, qui stocke entre autres, la date de derniere execution du script qui mets a jour la table aggregée.
4. une table date, qui stocke la date, la semaine de l'année, l'année, etc.

Mais 4 tables sont donc:

1. FACT (fact_pk, site_fk, date_fk, insert_date, champ1, champ2, etc)
2. FACT_AGG (fact_agg_pk, site_id, date_week_id, insert_date, champ_agg1, champ_agg2, etc)
3. STATUS (status_pk, last_agg_update) cette table contient une seule ligne.
4. DATE (date_pk, week_nb, year_nb, et)

Chaque jour des nouvelles entrées sont inserées dans la table fait. Mais aussi certaines entrées précédentes sont corrigées.
Je dois ensuite aggreger ces nouvelles données et corriger les anciennes données deja aggregées.

Recaculer entierement la table (80 millions!) c'est pas envisageable, ca prends trop de temps,

alors je dois identifier les "nouvelles" données et ensuite les aggreger, pour ensuite faire un insert ou update dans ma table aggreegée.

J'ai donc quelque chose qui ressemble a ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Select 
fact.site_id,
date.week_nb,
aggregate(champ1) as champ_agg1,
aggregate(champ2) as champ_agg2,
(...)
FROM
FACT, DATE
WHERE
DATE.DATE_PK = FACT.DATE_FK
AND FACT.SITE_id in (select distinct(SITE_id) from FACT where insert_date > (select last_agg_update from STATUS)
GROUP BY
fact.site_id,
date.week_nb

Il me semble que le morceau qui coince c'est ce select:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select distinct(SITE_FK) from FACT where insert_date > (select last_agg_update from STATUS)

Voila. J'ai pensé a partitionner ma table, selon le week_nb (et donc que j'ajouterais comme champ supplémentaire a niveau de la table FACT), il y a 4 ans d'histoire donc ca ferait 250 partitions.
Mais le souci c'est c'est que c'est pas a cet endroit que ca bloque (comme dit plus haut).

Sinon j'ai des index sur site_id, date_fk date_fk et insert_Date.

Avez vous des idées qui me permetterait d'optimiser ceci?

N'hesitez pas, je prends toute proposition!

Merci d'avance!

**pachot** · 26/10/2009, 22h36

Bonjour,
1-Il faudrait faire un explain plan.
2-Chaque fois que la reqête est lancée comien de sites sont concernés par des mises à jour ?
Cordialement,
Franck.

**pacmann** · 26/10/2009, 23h22

Salut !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
AND FACT.SITE_id IN (SELECT DISTINCT(SITE_id) FROM FACT WHERE insert_date > (SELECT last_agg_update FROM STATUS)

Il vaut peut être mieux mettre directement la condition, non ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
FACT.insert_date > (SELECT last_agg_update FROM STATUS)

(Au passage, quand tu "IN", tu n'as pas besoin de DISTINCT qui est fait automatiquement...)

Sinon, je n'ai pas trop bien compris pourquoi tu penses que le partitionnement (directement sur insert_date, par tranche de semaines) ne t'apportera rien... à priori ton gros prédicat est sur la date.

Enfin, je sais pas trop ce que ça vaut, mais dans la mesure où ta requête doit juste mettre à jour l'agrégation, ça vaut peut être le coup de tenter une vue matérialisée ?

Et puis tiens, si ta table date ne sers qu'à avoir le numéro de semaine, tu peux peut être t'en passer (sois dénormaliser, ou calculer la semaine seulement quand tu en as besoin) pour économiser la jointure ?

Quoi qu'il en soit, d'accord avec ce que dit Pachot concernant l'exec plan

**Karibou** · 27/10/2009, 10h41

Bonjour,

Tout d'abord merci de votre aide! précieuse!

Je vais essayer de repondre a vos questions:

Chaque fois que la reqête est lancée comien de sites sont concernés par des mises à jour ?

C'est difficile de comptabiliser combien de sites sont concernés par la mise a jour, puisque ca arrive quelques fois qu'ils se rendent compte d'une erreur et qu'ils corrigent un grosse parties des valeurs.
Mais j'imagine que c'est en general entre 50 000 et 100 000 sites.

Il vaut peut être mieux mettre directement la condition, non ?

non parce qu'il suffit qu'une valeur journaliere d'un site change pour que je doive recalculer toute la valeur pour la semaine.
Si je mets directement la conditon, ca va uniquement faire les calculs d'aggregation sur la valeur qui a changé et pas l'ensemble des valeurs de la semaine, ce qui serait donc faux.

(Au passage, quand tu "IN", tu n'as pas besoin de DISTINCT qui est fait automatiquement...)

Merci pour l'info, je savais pas, j'avais mis ca dans l'espoir que ca "prendrait moins de place" en memoire.

Sinon, je n'ai pas trop bien compris pourquoi tu penses que le partitionnement (directement sur insert_date, par tranche de semaines) ne t'apportera rien... à priori ton gros prédicat est sur la date.

Bah en fait pas vraiment, puisque je fais le calcul sur la semaine relative a la mesure et insert_date est la date de mise a jour, ou d'insertion d'une nouvelle valeur.
Donc en gros je peux avoir une mesure de pour la janvier 2009 avec une insert date d'hier puisque le champ a été mis a jour. C'est pourquoi j'ai du mal a trouver une regle de partitionnement.

Enfin, je sais pas trop ce que ça vaut, mais dans la mesure où ta requête doit juste mettre à jour l'agrégation, ça vaut peut être le coup de tenter une vue matérialisée ?

Une vue matérialisée de 80 millions de lignes ce sera suffisamment performant?

Bon sinon voici ma requete en entier:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
135
136
137
138
139
140
141
142
143
144
145
146
SELECT
a.DT_DIM_FK as DT_DIM_FK,
a.ST_DIM_FK as ST_DIM_FK,
a.GEO_DIM_FK as GEO_DIM_FK,
a.PRIM_CO_DIM_FK as PRIM_CO_DIM_FK, 
a.PRIM_IN_DIM_FK as PRIM_IN_DIM_FK,
a.FTE_DIM_FK as FTE_DIM_FK,
a.STAT_DIM_PR_FK as STAT_DIM_PR_FK,
a.STAT_DIM_RE_FK as STAT_DIM_RE_FK,
a.STAT_DIM_ST_FK as STAT_DIM_ST_FK,
a.PR_DATES_DIM_FK as PR_DATES_DIM_FK,
a.RE_DATES_DIM_FK as RE_DATES_DIM_FK,
a.ST_DATES_DIM_FK as ST_DATES_DIM_FK,
a.SJ_REQ_NUM as SJ_REQ_NUM,
a.SJ_REQ_SPR_NUM as SJ_REQ_SPR_NUM,
a.SJ_CMT_NUM as SJ_CMT_NUM,
a.SJ_CMT_SPR_NUM as SJ_CMT_SPR_NUM,
a.SJ_INI_EXP_NUM as SJ_INI_EXP_NUM,
a.SJ_EXP_NUM as SJ_EXP_NUM,
a.SJ_EXP_SPR_NUM as SJ_EXP_SPR_NUM,
a.SJ_EXP_SPR_BASELINE_NUM as SJ_EXP_SPR_BASELINE_NUM,
a.SJ_CNS_NUM as SJ_CNS_NUM,
a.SJ_ACT_MAX_NUM as SJ_ACT_MAX_NUM,
a.SJ_ACT_LAST_NUM as SJ_ACT_LAST_NUM,
a.SJ_SCR_NUM as SJ_SCR_NUM,
a.SJ_ENR_NUM as SJ_ENR_NUM,
a.SJ_TRM_MAX_NUM as SJ_TRM_MAX_NUM,
a.SJ_TRM_LAST_NUM as SJ_TRM_LAST_NUM,
a.SJ_CMP_NUM as SJ_CMP_NUM,
a.SJ_DTR_NUM as SJ_DTR_NUM,
a.SJ_ETR_NUM as SJ_ETR_NUM,
a.SJ_SCF_NUM as SJ_SCF_NUM,
a.SJ_FUP_NUM as SJ_FUP_NUM,
a.ST_REQ_NUM as ST_REQ_NUM,
a.ST_ACT_NUM as ST_ACT_NUM,
a.ST_NACT_NUM as ST_NACT_NUM,
a.ST_OPN_NUM as ST_OPN_NUM,
a.ST_ONHLD_NUM as ST_ONHLD_NUM,
a.ST_ENR_NUM as ST_ENR_NUM,
a.ST_TRM_NUM as ST_TRM_NUM,
a.ST_PLN_NUM as ST_PLN_NUM,
a.ST_REC_NUM as ST_REC_NUM, 
a.PR_CTMS_STAT as PR_CTMS_STAT,
a.RE_CTMS_STAT as RE_CTMS_STAT,
a.ST_CTMS_STAT as ST_CTMS_STAT,
a.CT_TIMESTAMP as CT_TIMESTAMP,
a.SJ_CUM_CNS_NUM as SJ_CUM_CNS_NUM,
a.SJ_CUM_SCR_NUM as SJ_CUM_SCR_NUM,
a.SJ_CUM_ENR_NUM as SJ_CUM_ENR_NUM,
a.SJ_CUM_CMP_NUM as SJ_CUM_CMP_NUM,
a.SJ_CUM_DTR_NUM as SJ_CUM_DTR_NUM,
a.SJ_CUM_ETR_NUM as SJ_CUM_ETR_NUM,
a.SJ_CUM_SCF_NUM as SJ_CUM_SCF_NUM,
a.SJ_CUM_FUP_NUM as SJ_CUM_FUP_NUM,
a.SJ_CUM_PR_CMT_NUM as SJ_CUM_PR_CMT_NUM,
a.SJ_CUM_PR_EXP_NUM as SJ_CUM_PR_EXP_NUM,
a.SJ_CUM_PR_INI_EXP_NUM as SJ_CUM_PR_INI_EXP_NUM,
a.SJ_CUM_RE_EXP_NUM as SJ_CUM_RE_EXP_NUM,
a.SJ_CUM_RE_INI_EXP_NUM as SJ_CUM_RE_INI_EXP_NUM,
a.dt_iso_wkofyr_nr as dt_iso_wkofyr_nr,
a.dt_iso_yr_nr as dt_iso_yr_nr,
T_CT_F_SITE_WK_ENROLL.ST_WK_FACTS_PK as ST_WK_FACTS_PK
FROM
(
SELECT
  T_CT_D_DATE.DT_WKOFYR_NR AS DT_DIM_FK,
  T_CT_F_SITE_ENROLL.ST_DIM_FK as ST_DIM_FK, 
  MAX(T_CT_F_SITE_ENROLL.GEO_DIM_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS GEO_DIM_FK, 
  MAX(T_CT_F_SITE_ENROLL.PRIM_CO_DIM_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS PRIM_CO_DIM_FK, 
  MAX(T_CT_F_SITE_ENROLL.PRIM_IN_DIM_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS PRIM_IN_DIM_FK, 
  MAX(T_CT_F_SITE_ENROLL.FTE_DIM_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS FTE_DIM_FK, 
  MAX(T_CT_F_SITE_ENROLL.STAT_DIM_PR_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS STAT_DIM_PR_FK, 
  MAX(T_CT_F_SITE_ENROLL.STAT_DIM_RE_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS STAT_DIM_RE_FK, 
  MAX(T_CT_F_SITE_ENROLL.STAT_DIM_ST_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS STAT_DIM_ST_FK, 
  MAX(T_CT_F_SITE_ENROLL.PR_DATES_DIM_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS PR_DATES_DIM_FK, 
  MAX(T_CT_F_SITE_ENROLL.RE_DATES_DIM_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS RE_DATES_DIM_FK, 
  MAX(T_CT_F_SITE_ENROLL.ST_DATES_DIM_FK) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS ST_DATES_DIM_FK,
  MAX(T_CT_F_SITE_ENROLL.SJ_REQ_NUM) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_REQ_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_REQ_SPR_NUM) AS SJ_REQ_SPR_NUM,
  MAX(T_CT_F_SITE_ENROLL.SJ_CMT_NUM) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CMT_NUM,
  SUM(T_CT_F_SITE_ENROLL.SJ_CMT_SPR_NUM) AS SJ_CMT_SPR_NUM,  
  MAX(T_CT_F_SITE_ENROLL.SJ_INI_EXP_NUM) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_INI_EXP_NUM, 
  MAX(T_CT_F_SITE_ENROLL.SJ_EXP_NUM) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_EXP_NUM,
  SUM(CASE
         WHEN ((TRUNC(T_CT_D_DATE.DT_DATE) >= T_CT_D_SITE.ST_FSJ_ENR_EXDT) AND (TRUNC(T_CT_D_DATE.DT_DATE) <= T_CT_D_SITE.ST_LSJ_ENR_EXDT))
        THEN T_CT_F_SITE_ENROLL.SJ_EXP_SPR_NUM
         ELSE 0
      END) AS SJ_EXP_SPR_NUM,    
  SUM(CASE
         WHEN ((TRUNC(T_CT_D_DATE.DT_DATE) >= T_CT_D_SITE.ST_FSJ_ENR_PLDT) AND (TRUNC(T_CT_D_DATE.DT_DATE) <= T_CT_D_SITE.ST_LSJ_ENR_PLDT))
        THEN T_CT_F_SITE_ENROLL.SJ_EXP_SPR_BASELINE_NUM
         ELSE 0
       END) AS SJ_EXP_SPR_BASELINE_NUM,  
  SUM(T_CT_F_SITE_ENROLL.SJ_CNS_NUM) AS SJ_CNS_NUM, 
  MAX(T_CT_F_SITE_ENROLL.SJ_ACT_NUM) AS SJ_ACT_MAX_NUM,
  MAX(T_CT_F_SITE_ENROLL.SJ_ACT_NUM) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_ACT_LAST_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_SCR_NUM) AS SJ_SCR_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_ENR_NUM) AS SJ_ENR_NUM, 
  MAX(T_CT_F_SITE_ENROLL.SJ_TRM_NUM) AS SJ_TRM_MAX_NUM,
  MAX(T_CT_F_SITE_ENROLL.SJ_TRM_NUM) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_TRM_LAST_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_CMP_NUM) AS SJ_CMP_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_DTR_NUM) AS SJ_DTR_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_ETR_NUM) AS SJ_ETR_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_SCF_NUM) AS SJ_SCF_NUM, 
  SUM(T_CT_F_SITE_ENROLL.SJ_FUP_NUM) AS SJ_FUP_NUM, 
  MAX(T_CT_F_SITE_ENROLL.ST_REQ_NUM) AS ST_REQ_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_ACT_NUM) AS ST_ACT_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_NACT_NUM) AS ST_NACT_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_OPN_NUM) AS ST_OPN_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_ONHLD_NUM) AS ST_ONHLD_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_ENR_NUM) AS ST_ENR_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_TRM_NUM) AS ST_TRM_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_PLN_NUM) AS ST_PLN_NUM,
  MAX(T_CT_F_SITE_ENROLL.ST_REC_NUM) AS ST_REC_NUM,
  MAX(T_CT_F_SITE_ENROLL.PR_CTMS_STAT) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS PR_CTMS_STAT, 
  MAX(T_CT_F_SITE_ENROLL.RE_CTMS_STAT) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS RE_CTMS_STAT, 
  MAX(T_CT_F_SITE_ENROLL.ST_CTMS_STAT) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS ST_CTMS_STAT,
  SYSDATE as CT_TIMESTAMP,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_cns_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_CNS_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_scr_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_SCR_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_enr_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_ENR_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_cmp_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_CMP_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_scf_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_SCF_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_etr_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_ETR_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_dtr_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_DTR_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_fup_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_FUP_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_pr_cmt_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_PR_CMT_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_pr_exp_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_PR_EXP_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_pr_ini_exp_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_PR_INI_EXP_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_re_exp_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_RE_EXP_NUM,
  MAX(T_CT_F_SITE_ENROLL.sj_cum_re_ini_exp_num) KEEP(DENSE_RANK LAST ORDER BY T_CT_D_DATE.DT_DATE) AS SJ_CUM_RE_INI_EXP_NUM,
  MAX(T_CT_D_DATE.dt_iso_wkofyr_nr) AS dt_iso_wkofyr_nr,
  MAX(T_CT_D_DATE.dt_iso_yr_nr) AS dt_iso_yr_nr
FROM 
  T_CT_F_SITE_ENROLL,
  T_CT_D_DATE,
  T_CT_D_SITE
WHERE 
  T_CT_D_DATE.DT_DIM_PK = T_CT_F_SITE_ENROLL.DT_DIM_FK
  AND T_CT_F_SITE_ENROLL.ST_DIM_FK in (select distinct(T_CT_F_SITE_ENROLL.ST_DIM_FK) from T_CT_F_SITE_ENROLL where trunc(ct_timestamp) > (select trunc(WK_PART_AGG_LST_UPDT) from CT.T_CT_ODS_STATUS))
  AND T_CT_F_SITE_ENROLL.ST_DIM_FK = T_CT_D_SITE.ST_DIM_PK
  AND T_CT_D_DATE.dt_date > sysdate - 180
GROUP BY 
T_CT_D_DATE.DT_WKOFYR_NR,
T_CT_F_SITE_ENROLL.ST_DIM_FK
) a left outer join T_CT_F_SITE_WK_ENROLL on (a.ST_DIM_FK=T_CT_F_SITE_WK_ENROLL.ST_DIM_FK and a.DT_DIM_FK = T_CT_F_SITE_WK_ENROLL.DT_DIM_AK)

Par rapport a mon exemple précédent:

T_CT_F_SITE_ENROLL = FACT
T_CT_F_SITE_WK_ENROLL = FACT_AGG aggregée a la semaine
T_CT_D_DATE = DATE

J'ai ajouté la clause

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND T_CT_D_DATE.dt_date > sysdate - 180

parce qu'on a juste besoin de faire la mise a jour des derniers 6 mois, au dela, ca mets moins de temps de tout reconstruire, ce qu'on fait tous les weekend pour prendre en compte les valeurs qui ont été modifiées pour des dates antérieures.

Je suis obligé de faire un join sur T_CT_F_SITE_WK_ENROLL pour savoir si je dois faire un update ou un insert.

Et voici l'explain plan:
Plan

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT STATEMENT  ALL_ROWSCost: 2,706,697  Bytes: 622,457  Cardinality: 797  									
	14 HASH JOIN OUTER  Cost: 2,706,697  Bytes: 622,457  Cardinality: 797  								
		12 VIEW CT. Cost: 2,675,814  Bytes: 625,645  Cardinality: 797  							
			11 SORT GROUP BY  Cost: 2,675,814  Bytes: 175,340  Cardinality: 797  						
				10 HASH JOIN  Cost: 326,928  Bytes: 8,326,516,000  Cardinality: 37,847,800  					
					1 TABLE ACCESS FULL TABLE CT.T_CT_D_SITE Cost: 1,188  Bytes: 2,840,132  Cardinality: 123,484  				
					9 HASH JOIN RIGHT SEMI  Cost: 320,957  Bytes: 7,834,494,600  Cardinality: 37,847,800  				
						5 VIEW VIEW SYS.VW_NSO_1 Cost: 20,094  Bytes: 9,462,655  Cardinality: 1,892,531  			
							4 TABLE ACCESS BY INDEX ROWID TABLE CT.T_CT_F_SITE_ENROLL Cost: 20,092  Bytes: 24,602,903  Cardinality: 1,892,531  		
								3 INDEX RANGE SCAN INDEX CT.T_CT_F_SITE_ENROLL_CT_DATE Cost: 746  Cardinality: 340,201  	
									2 TABLE ACCESS FULL TABLE CT.T_CT_ODS_STATUS Cost: 2  Bytes: 8  Cardinality: 1  
						8 HASH JOIN  Cost: 295,747  Bytes: 8,023,837,904  Cardinality: 37,848,292  			
							6 TABLE ACCESS FULL TABLE CT.T_CT_D_DATE Cost: 25  Bytes: 115,317  Cardinality: 4,271  		
							7 TABLE ACCESS FULL TABLE CT.T_CT_F_SITE_ENROLL Cost: 290,962  Bytes: 7,115,916,560  Cardinality: 37,850,620  		
		13 TABLE ACCESS FULL TABLE CT.T_CT_F_SITE_WK_ENROLL Cost: 30,148  Bytes: 105,197,580  Cardinality: 5,844,310

J'avoue que maitrise pas encore la compréhension d'un explain plan...

Merci encore de votre aide!

**pacmann** · 27/10/2009, 12h13

non parce qu'il suffit qu'une valeur journaliere d'un site change pour que je doive recalculer toute la valeur pour la semaine.
Si je mets directement la conditon, ca va uniquement faire les calculs d'aggregation sur la valeur qui a changé et pas l'ensemble des valeurs de la semaine, ce qui serait donc faux.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
FROM 
  T_CT_F_SITE_ENROLL,
...
WHERE ...
  AND T_CT_F_SITE_ENROLL.ST_DIM_FK IN 
(SELECT DISTINCT(T_CT_F_SITE_ENROLL.ST_DIM_FK) 
FROM T_CT_F_SITE_ENROLL WHERE trunc(ct_timestamp) > 
(SELECT trunc(WK_PART_AGG_LST_UPDT) FROM CT.T_CT_ODS_STATUS))

"Les lignes dont les identifiants sont dans la liste des identifiants dont la date est supérieure à..."
C'est exactement la même chose que :
"Les lignes dont dont la date est supérieure à..."
Non ?

Ensuite, il semble que tu veuilles faire un 'UPSERT' : update si matché, insert sinon.

Si ta version d'Oracle le permet, utilise MERGE.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
MERGE INTO LaCible
USING (TaRequeteSelect)
  ON (TonCritereDeMaching)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT...

C'est à priori bien plus efficace.

Bah en fait pas vraiment, puisque je fais le calcul sur la semaine relative a la mesure et insert_date est la date de mise a jour, ou d'insertion d'une nouvelle valeur.

Ben si je regarde le WHERE de ta requête, à part les jointures, il y a surtout la partie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
AND T_CT_F_SITE_ENROLL.ST_DIM_FK IN (SELECT DISTINCT(T_CT_F_SITE_ENROLL.ST_DIM_FK) FROM T_CT_F_SITE_ENROLL WHERE trunc(ct_timestamp) > (SELECT trunc(WK_PART_AGG_LST_UPDT) FROM CT.T_CT_ODS_STATUS))

Et comme dit, c'est bien le timestamp qui permet de faire la sélection...
Après, j'ai l'impression qu'on ne parlait pas de la même chose (peut être que tu pensais plus à la table cible à mettre à jour ?)

Concernant la vue matérialisée, je suis pas un spécialiste... mais dans la mesure où tu as du refresh incrémental, il est probable que ce soit assez bien foutu pour que la volumétrie "passive" n'est pas un impact totalement désastreux sur les performances ?

(Je regarderais ton plan plus tard...)

**pachot** · 27/10/2009, 13h38

Bonjour,

Vu que ton recalcul va devoir lire presque toutes les données (entre 50 000 et 100 000 sur 100000 au total) sur les 6 derniers mois, les index n'ont pas d'utilité.
Un partitionnement par date peut être utile pour ne faire un full scan que des 6 derniers mois par rapport aux 4 ans.
Enorme HASH JOIN et enorme GROUP BY: tu as interêt à définir une sort area size manuelle assez grande (vérifier que ces opérations ne se font pas en multipass)
Jen'ai pas bien compris ta requête. Ton besoin paraissait simple (cumul par semaine)
Est-ce que tu ne peut pas avoir une approche itérative: mettre à jour le cumul au fur et à mesure des insertions ?

Cordialement,
Franck.

Mise a jour d'une table aggregée

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager