[pb]Optimisation d'une procedure de suppression de doublons

**paddy** · 01/12/2004, 18h40

Bonjour ,

Je fais appel a vous pour me conseiller sur la maniere d'optimiser la procedure suivante .

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
 
 
CREATE OR REPLACE PROCEDURE P_DEL_DOUBLONS_FDET_DET_HISTO(p_dir_log IN VARCHAR2, p_fic_log IN VARCHAR2)
IS
	FILE_LOG       	UTL_FILE.FILE_TYPE; -- journal de traces
	NB_DET_BEFORE 	INTEGER ;
        NB_DET_AFTER  	INTEGER ;
	NB_DET_PURGE  	INTEGER ;
	chaine 		VARCHAR2(200);
	PROCEDURE tracer_info_operation(p_chaine IN VARCHAR2)
	IS
	BEGIN
		UTL_FILE.PUTF(FILE_LOG,'\n' || p_chaine);
	END;
 
	FUNCTION f_tab_count(p_nom_tab IN VARCHAR2) RETURN INTEGER
	IS
		retval INTEGER;
	BEGIN
		EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_nom_tab INTO retval;
		RETURN retval;
	END; 
BEGIN
	FILE_LOG := UTL_FILE.FOPEN(p_dir_log,p_fic_log,'W');
	NB_DET_BEFORE := f_tab_count('fdet_det_histo');
	chaine:=to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS ') ||' Nombre d''enregistrements dans fdet_det_histo avant purge : ' || NB_DET_BEFORE;
	tracer_info_operation(chaine);
	DELETE FROM fdet_det_histo T1
	WHERE EXISTS ( SELECT 1 
		FROM fdet_det_histo T2
		WHERE	T1.num_ecro_ini 	= T2.num_ecro_ini
		AND 	T1.cod_typ_evt_pen 		= T2.cod_typ_evt_pen
		AND 	T1.cod_cat_pen 			= T2.cod_cat_pen    
		AND 	NVL(T1.dcod_nbr_aff,'zwx')	= NVL(T2.dcod_nbr_aff,'zwx')   
		AND 	NVL(T1.cod_proc_pen,'zwx') 	= NVL(T2.cod_proc_pen,'zwx')   
		AND 	NVL(T1.cod_tran_age,'zwx') 	= NVL(T2.cod_tran_age,'zwx')   
		AND 	NVL(T1.cod_cat_adm,'zwx') 	= NVL(T2.cod_cat_adm,'zwx')    
		AND 	NVL(T1.num_ecro_cou,-99) 	= NVL(T2.num_ecro_cou,-99)   
		AND 	NVL(T1.cod_etab_penit,'zwx') 	= NVL(T2.cod_etab_penit,'zwx') 
		AND 	NVL(T1.dat_lib_det,to_date('01/01/1900','DD/MM/YYYY')) 	= NVL(T2.dat_lib_det,to_date('01/01/1900','DD/MM/YYYY'))    
		AND 	NVL(T1.nbr_enf_det,-99) 	= NVL(T2.nbr_enf_det,-99)    
		AND 	NVL(T1.nbr_enf_deten,-99) 	= NVL(T2.nbr_enf_deten,-99)  
		AND 	NVL(T1.dat_nais_det,to_date('01/01/1900','DD/MM/YYYY')) 	= NVL(T2.dat_nais_det,to_date('01/01/1900','DD/MM/YYYY'))   
		AND 	NVL(T1.ind_fran_det,'W') 	= NVL(T2.ind_fran_det,'W')   
		AND 	NVL(T1.ind_incar,'W') 		= NVL(T2.ind_incar,'W')      
		AND 	NVL(T1.sexe,'W') 		= NVL(T2.sexe,'W')           
		AND 	NVL(T1.dat_ecro_cou,to_date('01/01/1900','DD/MM/YYYY')) 	= NVL(T2.dat_ecro_cou,to_date('01/01/1900','DD/MM/YYYY'))   
		AND 	NVL(T1.dat_ecro_ini,to_date('01/01/1900','DD/MM/YYYY')) 	= NVL(T2.dat_ecro_ini,to_date('01/01/1900','DD/MM/YYYY'))   
		AND 	NVL(T1.dt_fin_pen_det,to_date('01/01/1900','DD/MM/YYYY')) 	= NVL(T2.dt_fin_pen_det,to_date('01/01/1900','DD/MM/YYYY')) 
		AND 	NVL(T1.dt_comd_det,to_date('01/01/1900','DD/MM/YYYY')) 	= NVL(T2.dt_comd_det,to_date('01/01/1900','DD/MM/YYYY'))    
		AND 	NVL(T1.qtm_ii_val,'W') 		= NVL(T2.qtm_ii_val,'W')     
		AND 	NVL(T1.qtm_hh_val,-99) 		= NVL(T2.qtm_hh_val,-99)     
		AND 	NVL(T1.qtm_mm_val,-99) 		= NVL(T2.qtm_mm_val,-99)     
		AND 	NVL(T1.qtm_jj_val,-99) 		= NVL(T2.qtm_jj_val,-99)     
		AND 	NVL(T1.qtm_mm_pen,-99) 		= NVL(T2.qtm_mm_pen,-99)     
		AND 	NVL(T1.qtm_aa_pen,'zwx') 	= NVL(T2.qtm_aa_pen,'zwx')     
		AND 	NVL(T1.qtm_ss_pen,-99) 		= NVL(T2.qtm_ss_pen,-99)     
		AND 	NVL(T1.qtm_hh_pen,-99) 		= NVL(T2.qtm_hh_pen,-99)     
		AND 	NVL(T1.qtm_jj_pen,-99) 		= NVL(T2.qtm_jj_pen,-99)     
		AND 	NVL(T1.sum_qtm_aa_pen,-99) 	= NVL(T2.sum_qtm_aa_pen,-99) 
		AND 	NVL(T1.sum_qtm_mm_pen,-99) 	= NVL(T2.sum_qtm_mm_pen,-99) 
		AND 	NVL(T1.sum_qtm_ss_pen,-99) 	= NVL(T2.sum_qtm_ss_pen,-99) 
		AND 	NVL(T1.sum_qtm_hh_pen,-99) 	= NVL(T2.sum_qtm_hh_pen,-99) 
		AND 	NVL(T1.sum_qtm_jj_pen,-99) 	= NVL(T2.sum_qtm_jj_pen,-99) 
		AND 	NVL(T1.sum_aa_rem_pen,-99) 	= NVL(T2.sum_aa_rem_pen,-99) 
		AND 	NVL(T1.sum_mm_rem_pen,-99) 	= NVL(T2.sum_mm_rem_pen,-99) 
		AND 	NVL(T1.sum_ss_rem_pen,-99) 	= NVL(T2.sum_ss_rem_pen,-99) 
		AND 	NVL(T1.sum_hh_rem_pen,-99) 	= NVL(T2.sum_hh_rem_pen,-99) 
		AND 	NVL(T1.sum_jj_rem_pen,-99) 	= NVL(T2.sum_jj_rem_pen,-99) 
		AND 	NVL(T1.age_det,-99) 		= NVL(T2.age_det,-99)        
		AND 	NVL(T1.ind_gide,'W') 		= NVL(T2.ind_gide,'W')       
		AND 	NVL(T1.libl_typ_evt_pen,'zxw') 	= NVL(T2.libl_typ_evt_pen,'zwx')
		AND 	NVL(T1.libc_cat_pen,'zwx') 	= NVL(T2.libc_cat_pen,'zwx')   
		AND 	NVL(T1.dlibl_nbr_aff,'zwx')	= NVL(T2.dlibl_nbr_aff,'zwx')  
		AND 	NVL(T1.libc_proc_pen,'zwx') 	= NVL(T2.libc_proc_pen,'zwx')  
		AND 	NVL(T1.libc_tran_age,'zwx') 	= NVL(T2.libc_tran_age,'zwx')  
		AND 	NVL(T1.libl_cat_adm,'zwx') 	= NVL(T2.libl_cat_adm,'zwx')   
		AND 	NVL(T1.grpevt_pen_det,-9) 	= NVL(T2.grpevt_pen_det,-9) 
		AND 	NVL(T1.qtm_tot_pen,-99) 	= NVL(T2.qtm_tot_pen,-99)    
		AND 	NVL(T1.qtm_tot_val,-99) 	= NVL(T2.qtm_tot_val,-99)    
		AND 	NVL(T1.cod_tra_peine,'zwx') 	= NVL(T2.cod_tra_peine,'')  
		AND 	NVL(T1.libl_tra_peine,'zwx') 	= NVL(T2.libl_tra_peine,'zwx') 
		AND 	NVL(T1.cod_natio,'zwx') 		= NVL(T2.cod_natio,'zwx')      
		AND 	NVL(T1.libl_natio,'zwx') 		= NVL(T2.libl_natio,'zwx')     
		AND 	NVL(T1.cod_typ_mvt,'zwx') 	= NVL(T2.cod_typ_mvt,'zwx')    
		AND 	NVL(T1.libc_typ_mvt,'zwx') 	= NVL(T2.libc_typ_mvt,'zwx')   
		AND 	NVL(T1.cod_mot_lib,'zwx') 	= NVL(T2.cod_mot_lib,'zwx')    
		AND 	NVL(T1.cod_typ_mes,'zwx')		= NVL(T2.cod_typ_mes,'zwx')    
		AND 	NVL(T1.cod_niv_ins,'zwx') 	= NVL(T2.cod_niv_ins,'zwx')    
		AND 	NVL(T1.libl_niv_ins,'zwx') 	= NVL(T2.libl_niv_ins,'zwx')   
		AND 	NVL(T1.libl_typ_mes,'zwx') 	= NVL(T2.libl_typ_mes,'zwx')   
		AND 	NVL(T1.dat_mvt_det,to_date('01/01/1900','DD/MM/YYYY')) 	= NVL(T2.dat_mvt_det,to_date('01/01/1900','DD/MM/YYYY'))    
		AND	T2.dat_der_modif = (	SELECT	MAX(T3.dat_der_modif)
						FROM	fdet_det_histo T3
						WHERE	T3.num_ecro_ini = T2.num_ecro_ini
						AND	T3.dat_der_modif < T1.dat_der_modif
					   )
		);
		COMMIT;
		NB_DET_AFTER := SQL%ROWCOUNT;
		NB_DET_PURGE := NB_DET_BEFORE - NB_DET_AFTER;
        	chaine:=to_char(SYSDATE,'DD/MM/YYYY HH24:MI:SS ') ||' Nombre d''enregistrements dans fdet_det_histo apres purge : ' || NB_DET_PURGE;
                tracer_info_operation(chaine); 
		UTL_FILE.FCLOSE(FILE_LOG);
	EXCEPTION
		WHEN OTHERS 
		THEN 	ROLLBACK;
			chaine:='Probleme lors de la purge de fdet_det_histo ' || SQLERRM(SQLCODE);
			tracer_info_operation(chaine);
			UTL_FILE.FCLOSE(FILE_LOG);
 
END P_DEL_DOUBLONS_FDET_DET_HISTO;
/

Le but est
de supprimer les lignes identiques entre deux dates consécutives .
Un index unique est crée sur les colonnes suivantes :
NUM_ECRO_INI et DAT_DER_MODIF

la table contient 1,600,000 données .

La procédure s'effectue normalement avec le resultat attendu mais au bout de 9heures .

La configuration est la suivante : Serveur Linux , p III 1,2Ghz 512 mo, Oracle 8i 8.1.7.0.1

3 groupes de REDO LOG de 100 Mo

Merci de votre Aide

**orafrance** · 01/12/2004, 18h54

Et pourquoi pas :

Code :

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

1
2
3
4
5
CREATE TABLE fdet_det_histo_arch AS SELECT DISTINCT * FROM fdet_det_histo;
 
DROP TABLE fdet_det_histo;
 
ALTER TABLE fdet_det_histo_arch RENAME TO fdet_det_histo_arch;

**paddy** · 01/12/2004, 19h19

à la date d1 on a certaines données
à la date d2 on a les même (donc doublons)
etc.....
à la date dn-2 on a des données différentes
à la date dn-1 on la les même données (que dn-2)
à la date dn on a les mêmes données que d1 ( mais on ne doit pas les supprimer cette fois-ci)
c'est là la difficulté (on ne peut malheureusement donc pas faire votre solution

( SELECT DISTINCT * ....) )

Merci de votre aide.

**orafrance** · 01/12/2004, 19h27

c'était l'idée qu'il fallait retenir

Voila qui devrait répondre mieux à la problématique :

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
SELECT num_ecro_ini    
      ,    T1.cod_typ_evt_pen 
      ,    T1.cod_cat_pen     
      ,    dcod_nbr_aff 
      ,    cod_proc_pen 
      ,    cod_tran_age 
      ,    cod_cat_adm  
      ,    num_ecro_cou
      ,    cod_etab_penit   
      ,    dat_lib_det    
      ,    nbr_enf_det    
      ,    nbr_enf_deten  
      ,    dat_nais_det   
      ,    ind_fran_det    
      ,    ind_incar       
      ,    sexe            
      ,    dat_ecro_cou    
      ,    dat_ecro_ini    
      ,    dt_fin_pen_det  
      ,    dt_comd_det    
      ,    qtm_ii_val      
      ,    qtm_hh_val      
      ,    qtm_mm_val      
      ,    qtm_jj_val      
      ,    qtm_mm_pen    
      ,    qtm_aa_pen    
      ,    qtm_ss_pen    
      ,    qtm_hh_pen    
      ,    qtm_jj_pen    
      ,    sum_qtm_aa_pen
      ,    sum_qtm_mm_pen
      ,    sum_qtm_ss_pen
      ,    sum_qtm_hh_pen
      ,    sum_qtm_jj_pen
      ,    sum_aa_rem_pen
      ,    sum_mm_rem_pen
      ,    sum_ss_rem_pen
      ,    sum_hh_rem_pen
      ,    sum_jj_rem_pen
      ,    age_det       
      ,    ind_gide      
      ,    libl_typ_evt_pen
      ,    libc_cat_pen    
      ,    dlibl_nbr_aff   
      ,    libc_proc_pen   
      ,    libc_tran_age   
      ,    libl_cat_adm    
      ,    grpevt_pen_det
      ,    qtm_tot_pen   
      ,    qtm_tot_val   
      ,    cod_tra_peine 
      ,    libl_tra_peine
      ,    cod_natio     
      ,    libl_natio    
      ,    cod_typ_mvt   
      ,    libc_typ_mvt  
      ,    cod_mot_lib   
      ,    cod_typ_mes   
      ,    cod_niv_ins   
      ,    libl_niv_ins  
      ,    libl_typ_mes  
      ,    dat_mvt_det   
      ,    MAX(dat_der_modif)
      FROM fdet_det_histo
      GROUP BY num_ecro_ini    
      ,    T1.cod_typ_evt_pen 
      ,    T1.cod_cat_pen     
      ,    dcod_nbr_aff 
      ,    cod_proc_pen 
      ,    cod_tran_age 
      ,    cod_cat_adm  
      ,    num_ecro_cou
      ,    cod_etab_penit   
      ,    dat_lib_det    
      ,    nbr_enf_det    
      ,    nbr_enf_deten  
      ,    dat_nais_det   
      ,    ind_fran_det    
      ,    ind_incar       
      ,    sexe            
      ,    dat_ecro_cou    
      ,    dat_ecro_ini    
      ,    dt_fin_pen_det  
      ,    dt_comd_det    
      ,    qtm_ii_val      
      ,    qtm_hh_val      
      ,    qtm_mm_val      
      ,    qtm_jj_val      
      ,    qtm_mm_pen    
      ,    qtm_aa_pen    
      ,    qtm_ss_pen    
      ,    qtm_hh_pen    
      ,    qtm_jj_pen    
      ,    sum_qtm_aa_pen
      ,    sum_qtm_mm_pen
      ,    sum_qtm_ss_pen
      ,    sum_qtm_hh_pen
      ,    sum_qtm_jj_pen
      ,    sum_aa_rem_pen
      ,    sum_mm_rem_pen
      ,    sum_ss_rem_pen
      ,    sum_hh_rem_pen
      ,    sum_jj_rem_pen
      ,    age_det       
      ,    ind_gide      
      ,    libl_typ_evt_pen
      ,    libc_cat_pen    
      ,    dlibl_nbr_aff   
      ,    libc_proc_pen   
      ,    libc_tran_age   
      ,    libl_cat_adm    
      ,    grpevt_pen_det
      ,    qtm_tot_pen   
      ,    qtm_tot_val   
      ,    cod_tra_peine 
      ,    libl_tra_peine
      ,    cod_natio     
      ,    libl_natio    
      ,    cod_typ_mvt   
      ,    libc_typ_mvt  
      ,    cod_mot_lib   
      ,    cod_typ_mes   
      ,    cod_niv_ins   
      ,    libl_niv_ins  
      ,    libl_typ_mes  
      ,    dat_mvt_det

Quand aux indexes, il sont inutiles un FULL SCAN de la table sera obligatoire

**paddy** · 01/12/2004, 20h01

Je vais exposer plus la difficultée :
je m'excuse par avance si j'ai pas été assez précis:

car effectivement j'ai pensé à cette solution mais elle ne repondait pas aux problèmes.

Exemple :

Source :

NUM_ECRO_INI DATE_DER_MODIF DATA1
------------------ ---------------------- --------
1 00002 01/12/2004 CO
2 00002 02/12/2004 CO
3 00002 03/12/2004 PRE
4 00002 04/12/2004 PRE
5 00002 05/12/2004 CO
6 00002 06/12/2004 CO

La version GROUP BY gardera les lignes 1 et 3

Or

Le Resultat cherché est de garder les lignes 1,3 et 5

en gros chaque nouveau changements doit être conservé

Merci encore .

**lalystar** · 02/12/2004, 05h21

Envoyé par paddy

en gros chaque nouveau changements doit être conservé

Donc pour un même num_ecro_ini chaque ligne identique à la précédente (ordonnée par date_der_modif) doit être supprimée.

En partant de ton exemple simplifié :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> select * 
  2  from   TEST;
 
        ID DTE      DATA
---------- -------- -----
         1 01/12/04 CO
         1 02/12/04 CO
         1 03/12/04 PRE
         1 04/12/04 PRE
         1 05/12/04 CO
         1 06/12/04 CO

Ca revient à faire exactement ca :

Code :

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

1
2
3
 
SQL> delete TEST
  2  where  data = lag(data) over(partition by id order by dte);

Malheureusement on ne peut pas utiliser de fonctions analytiques dans une clause WHERE !

On doit donc décomposer. On commence par créer une table des enregistrements à garder :

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
 
SQL> create table TO_KEEP as
  2  select  row_id
  3  from
  4     (
  5    select 
  6         rowid as row_id,
  7         case
  8            when data = lag(data) over(partition by id order by dte) then 1
  9         end poubelle
 10    from     TEST
 11   )
 12  where  poubelle is null;
 
SQL> select * from TO_KEEP;
 
ROW_ID
------------------
AAAMW2AAEAAAATNAAA
AAAMW2AAEAAAATNAAC
AAAMW2AAEAAAATNAAE

Si tu estimes que tu as bcp de doublons à supprimer : il vaut mieux créer une nouvelle table :

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
 
SQL> create table NEW_TEST as
  2  select a.*
  3  from   TEST a, TO_KEEP b
  4  where  a.rowid = b.row_id;
 
Table created.
 
SQL> select * 
  2  from   NEW_TEST;
 
        ID DTE      DATA
---------- -------- -----
         1 01/12/04 CO
         1 03/12/04 PRE
         1 05/12/04 CO

Il reste à supprimer l'ancienne et renommer la nouvelle table.

Si tu estimes que tu n'as pas bcp de doublons à supprimer, il vaut mieux faire un DELETE dans la table d'origine.

Pour revenir à ton pb initial, DATA est équivalent à la concaténation de tout tes champs sauf num_ecro_ini et dat_der_modif séparés par qqchose de ton choix comme '|' par ex. (ca permet la comparaison même des éléments nulls) :

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
 
create table TO_KEEP as
select 	row_id
from
   (
		select	rowid as row_id,
   				case
      				when data = lag(data) over(partition by id order by dte) then 1
        			end poubelle
		from     
			(
				select	num_ecro_ini, dat_der_modif,
							cod_typ_evt_pen || '|' ||
      					cod_cat_pen || '|' ||
      					...     
				from		FDET_DET_HISTO
			)
	)
where		poubelle is null

Est-ce-que ca résoud ton pb ? Quelles sont les perfs ?

J'ai l'impression qu'il y a bcp plus simple, mais je vois pas comment !

Laly.

**lalystar** · 02/12/2004, 09h25

Comme il risque d'y avoir bcp de tris avec l'utilisation de la fonction analytique, il peut être utile d'augmenter fortement le paramètre SORT_AREA_SIZE au niveau de la session.

Laly.

**orafrance** · 02/12/2004, 09h34

Ou le TEMP parce que si la requête prend 9h c'est qu'il y a du volume et à moins d'avoir plus Giga de RAM ça risque d'être dûr à assumer

**lalystar** · 02/12/2004, 09h40

Je suis très optimiste sur le temps de réponse avec les fonctions analytiques.

Paddy si tu testes cette méthode, je serais très intéressé du résultat et de la perf.

Laly.

**orafrance** · 02/12/2004, 09h49

moi aussi, mais je le suis moins concernant la consommation du tri

**paddy** · 02/12/2004, 11h21

Merci pour vos reponses
je prepare mon test et je vous tiens au courant

**lalystar** · 02/12/2004, 11h22

Envoyé par orafrance

moi aussi, mais je le suis moins concernant la consommation du tri

Comparé au premier group by, le TEMP sera bcp moins sollicité amha.

Laly.

**orafrance** · 02/12/2004, 11h26

certes

**paddy** · 03/12/2004, 09h35

Bonjour,

Merci Laly pour l'information sur les fonctions analytiques .

Donc j'ai initialisé le Cache de TRIE de 128 Ko -> 1024Ko

je passe donc de la solution ensembliste de Plus de 9h

à la solution qui utilise les fonctions analytiques

à 25minutes.

Pour le @./CREATE_FDET_DET_HISTO_TO_KEEP.sql

Table/Scan 2min33 FDET_DET_HISTO
Sort/Merge 6min35
Sort Output 5min56

Pour le @./CREATE_FDET_DET_HISTO_NEW.sql

10 min

Merci encore ...

J'aurais un autre pb sur des curseurs , juste pour savoir comment je peu utiliser les fonctions analytiques si c necessaire.

PS: Lalay .. un exemple comme celui ci dans ta doc serai tres demonstratif .

8)

**lalystar** · 03/12/2004, 09h41

Tu es en Oracle 8i ? Si c'est le cas, le moteur PL/SQL ne reconnait pas les fonctions analytiques. Tu es obligé de les utiliser dans du SQL dynamique (EXECUTE IMMEDIATE par ex. ou bien REF CURSOR).

Laly.

**orafrance** · 03/12/2004, 09h49

belle démonstration de l'utilité des fonctions analytiques

**paddy** · 03/12/2004, 11h03

Voici la solution par rapport à me premier source

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
 
CREATE TABLE FDET_DET_HISTO_TO_KEEP 
AS
SELECT	row_id
FROM
   (
      SELECT rowid as row_id,
             case
	         when data = lag(data) over(partition by num_ecro_ini order by dat_der_modif) then 1
	     end poubelle
      FROM
	 (
	    SELECT num_ecro_ini, dat_der_modif,
		   cod_typ_evt_pen || '|' ||
		   cod_cat_pen || '|' ||
		   dcod_nbr_aff || '|' ||
		   cod_proc_pen || '|' ||
		   cod_tran_age || '|' ||
		   cod_cat_adm || '|' ||
		   num_ecro_cou || '|' ||
		   cod_etab_penit || '|' ||
		   dat_lib_det || '|' ||
		   nbr_enf_det || '|' ||
		   nbr_enf_deten || '|' ||
		   dat_nais_det || '|' ||
		   ind_fran_det || '|' ||
		   ind_incar || '|' ||
		   sexe || '|' ||
		   dat_ecro_cou || '|' ||
		   dat_ecro_ini || '|' ||
		   dt_fin_pen_det || '|' ||
		   dt_comd_det || '|' ||
		   qtm_ii_val || '|' ||
		   qtm_hh_val || '|' ||
		   qtm_mm_val || '|' ||
		   qtm_jj_val || '|' ||
		   qtm_mm_pen || '|' ||
		   qtm_aa_pen || '|' ||
		   qtm_ss_pen || '|' ||
		   qtm_hh_pen || '|' ||
		   qtm_jj_pen || '|' ||
		   sum_qtm_aa_pen || '|' ||
		   sum_qtm_mm_pen || '|' ||
		   sum_qtm_ss_pen || '|' ||
		   sum_qtm_hh_pen || '|' ||
		   sum_qtm_jj_pen || '|' ||
		   sum_aa_rem_pen || '|' ||
		   sum_mm_rem_pen || '|' ||
		   sum_ss_rem_pen || '|' ||
		   sum_hh_rem_pen || '|' ||
		   sum_jj_rem_pen || '|' ||
		   age_det || '|' ||
		   ind_gide || '|' ||
		   libl_typ_evt_pen || '|' ||
		   libc_cat_pen || '|' ||
		   dlibl_nbr_aff || '|' ||
		   libc_proc_pen || '|' ||
		   libc_tran_age || '|' ||
		   libl_cat_adm || '|' ||
		   grpevt_pen_det || '|' ||
		   qtm_tot_pen || '|' ||
		   qtm_tot_val || '|' ||
		   cod_tra_peine || '|' ||
		   libl_tra_peine || '|' ||
		   cod_natio || '|' ||
		   libl_natio || '|' ||
		   cod_typ_mvt || '|' ||
		   libc_typ_mvt || '|' ||
		   cod_mot_lib || '|' ||
		   cod_typ_mes || '|' ||
		   cod_niv_ins || '|' ||
		   libl_niv_ins || '|' ||
		   libl_typ_mes || '|' ||
		   dat_mvt_det  
		   data
	    FROM   FDET_DET_HISTO
	 )	
   )
WHERE poubelle is null
/

Puis reconstruction de la nouvelle table

Code :

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

1
2
3
4
5
6
 
CREATE TABLE FDET_DET_HISTO_NEW
AS
SELECT a.*
FROM FDET_DET_HISTO a, FDET_DET_HISTO_TO_KEEP b
WHERE a.rowid = b.row_id

**paddy** · 03/12/2004, 11h17

Voic i mon code avec curseur , je suis bien sur 8i

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
 
	FOR V_ENR IN C_FDET_DET
        LOOP
		BEGIN
                	-- SUPPRESSION DES DOUBLONS DANS LA TABLES DES INFRACTIONS
                        -- =======================================================
        		OPEN CDAT_DER_INF(V_ENR.num_ecro_ini);
        		FETCH CDAT_DER_INF INTO DATE1;
 
			LOOP
                		FETCH CDAT_DER_INF INTO DATE2;
				EXIT WHEN CDAT_DER_INF%NOTFOUND ;
                		-- Enregistrement Ã upprimmer
				-----------------------------
				-- 1er Test : si le nombre d'occurrence entre 2 dates est identique
				SELECT COUNT(*) 
				INTO	v_nb_record_1
				FROM 	fdet_inf_histo 
				WHERE 	dat_der_modif = DATE1 
				AND num_ecro_ini = V_ENR.num_ecro_ini;
 
				SELECT COUNT(*) 
				INTO	v_nb_record_2
				FROM 	fdet_inf_histo 
				WHERE 	dat_der_modif = DATE2 
				AND num_ecro_ini = V_ENR.num_ecro_ini;
 
				IF v_nb_record_1=v_nb_record_2
				THEN
					-- 2Ã¨ test : Existe-t-il des rangs infractions distincts
					-- pour le mÃª nombre d'occurence
					SELECT 	SUM(rang_inf)
					INTO 	v_somme_record_1
					FROM 	fdet_inf_histo 
					WHERE dat_der_modif = DATE1 
					AND num_ecro_ini = V_ENR.num_ecro_ini;
 
					SELECT 	SUM(rang_inf)
					INTO 	v_somme_record_2
					FROM 	fdet_inf_histo 
					WHERE dat_der_modif = DATE2 
					AND num_ecro_ini = V_ENR.num_ecro_ini;
 
					IF v_somme_record_1=v_somme_record_2
					THEN
					-------
						DELETE FROM  fdet_inf_histo
     						WHERE dat_der_modif = DATE2
     						AND   num_ecro_ini = V_ENR.num_ecro_ini
     						AND   NOT EXISTS ( 
                  					SELECT  1
                  					FROM    ( SELECT *
                                				FROM fdet_inf_histo
                                				WHERE dat_der_modif = DATE1
                                				AND num_ecro_ini = V_ENR.num_ecro_ini) INF1
                  					WHERE   EXISTS (
                              						SELECT  1
                              						FROM (  SELECT *
                                        					FROM fdet_inf_histo
                                        					WHERE dat_der_modif = DATE2
                                        					AND num_ecro_ini = V_ENR.num_ecro_ini) INF2
                              						WHERE   INF1.rang_inf = INF2.rang_inf
									AND (   INF1.cod_nat_inf != INF2.cod_nat_inf
                                      				OR INF1.dat_deb_inf_maj    != INF2.dat_deb_inf_maj
                                      				OR INF1.cod_fam_stat       != INF2.cod_fam_stat
                                      				OR INF1.cod_etab_penit     != INF2.cod_etab_penit
                                      				OR INF1.num_ecro_cou       != INF2.num_ecro_cou
                                		OR NVL(INF1.cod_mod_parti,'zwx')!= NVL(INF2.cod_mod_parti,'zwx')
                                      		OR NVL(INF1.libl_fam_stat,'zwx') != NVL(INF2.libl_fam_stat,'zwx')
                                      		OR NVL(INF1.prior_fam_stat,-99) != NVL(INF2.prior_fam_stat,-99)
                                   				) -- and
                          				) -- exists
     						);
					----------------------
 
						IF SQL%NOTFOUND
						THEN 
							v_doublons:=TRUE;	
						END IF;
					ELSE
						v_doublons:=TRUE;
					END IF;
				ELSE
					v_doublons:=TRUE;
				END IF;
				IF v_doublons=TRUE THEN
					DATE1:=DATE2;	
				END IF;
				v_doublons:=FALSE;
			END LOOP;  -- fin boucles infraction
			CLOSE CDAT_DER_INF;
		EXCEPTION
                	WHEN OTHERS
                        THEN
                        	ROLLBACK;
                                V_CHAINE := 'INFRACTIONS : '|| 'Problème de suppression des infractions de ' || V_ENR.num_ecro_ini || ':'||SQLERRM(SQLCODE);
 
		END ; -- fin traitement infractions

et l'autre qui suit derriere

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
 
BEGIN
                -- SUPPRESSION DES DOUBLONS DANS LA TABLES DES MESURES
                -- =======================================================
                	OPEN CDAT_DER_MES(V_ENR.num_ecro_ini);
                        FETCH CDAT_DER_MES INTO DATE1;
                	LOOP
                		FETCH CDAT_DER_MES INTO DATE2;
				EXIT WHEN CDAT_DER_MES%NOTFOUND ;
                                -- Enregistrement à supprimmer
				------------------------------
				-- 1er Test : si le nombre d'occurrence entre 2 dates est identique
				SELECT COUNT(*) 
				INTO	v_nb_record_1
				FROM 	fdet_mes_histo 
				WHERE 	dat_der_modif = DATE1 
				AND num_ecro_ini = V_ENR.num_ecro_ini;
 
				SELECT COUNT(*) 
				INTO	v_nb_record_2
				FROM 	fdet_mes_histo 
				WHERE 	dat_der_modif = DATE2 
				AND num_ecro_ini = V_ENR.num_ecro_ini;
 
				IF v_nb_record_1=v_nb_record_2
				THEN
					SELECT 	SUM(rang_mes)
					INTO 	v_somme_record_1
					FROM 	(SELECT * 
							FROM fdet_mes_histo 
							WHERE dat_der_modif = DATE1 
							AND num_ecro_ini = V_ENR.num_ecro_ini);
 
					SELECT 	SUM(rang_mes)
					INTO 	v_somme_record_2
					FROM 	(SELECT * 
							FROM fdet_mes_histo 
							WHERE dat_der_modif = DATE2 
							AND num_ecro_ini = V_ENR.num_ecro_ini);
 
					IF v_somme_record_1=v_somme_record_2
					THEN
					---
						DELETE FROM  fdet_mes_histo
     						WHERE dat_der_modif = DATE2
     						AND   num_ecro_ini = V_ENR.num_ecro_ini
     						AND   NOT EXISTS (
                        				SELECT  1
                        				FROM    ( SELECT *
                                        			FROM fdet_mes_histo
                                        			WHERE dat_der_modif = DATE1
                                        			AND num_ecro_ini = V_ENR.num_ecro_ini) MES1
                        				WHERE   EXISTS (
                                        				SELECT  1
                                        				FROM (  SELECT *
                                                				FROM fdet_mes_histo
                                                				WHERE dat_der_modif = DATE2
                                                				AND num_ecro_ini = V_ENR.num_ecro_ini) MES2
                                        				WHERE   MES1.rang_mes = MES2.rang_mes
									AND (   MES1.cod_typ_mes != MES2.cod_typ_mes
                                                		OR NVL(MES1.qtm_aa_mes,'zwx') != NVL(MES2.qtm_aa_mes,'zwx')
                                                OR      NVL(MES1.qtm_mm_mes,-99)  != NVL(MES2.qtm_mm_mes,-99)
                                                OR      NVL(MES1.qtm_jou_mes,-99)         != NVL(MES2.qtm_jou_mes,-99)
                                                OR      NVL(MES1.qtm_heu_mes,-99)         != NVL(MES2.qtm_heu_mes,-99)
                                                OR      NVL(MES1.qtm_sem_mes,-99)         != NVL(MES2.qtm_sem_mes,-99)
                                                OR      MES1.cod_etab_penit     != MES2.cod_etab_penit
                                                OR      MES1.num_ecro_cou       != MES2.num_ecro_cou
                                        ) -- and
                                ) -- exists
                ); -- not exists
					---
 
						IF SQL%NOTFOUND
						THEN 
							v_doublons:=TRUE;	
						END IF;
					ELSE
						v_doublons:=TRUE;
					END IF;
				ELSE
					v_doublons:=TRUE;
				END IF;
				IF v_doublons=TRUE THEN
					DATE1:=DATE2;	
				END IF;
				v_doublons:=FALSE;
                	END LOOP;
                	CLOSE CDAT_DER_MES;
                EXCEPTION
                	WHEN OTHERS
                        THEN
                        	ROLLBACK;
                                V_CHAINE := 'MESURES : '|| 'Problème de suppression des mesures de ' || V_ENR.num_ecro_ini || ':'||SQLERRM(SQLCODE);
                END ; -- FIN TRAITEMNT MESURES
 
		COMMIT;  -- fin de traitement pour un detenu	
	END LOOP ; -- FIN DU TRAITEMENT POUR 1 DÃENU !
                        --

Ce traitement me prend trois heures .

C'est juste pour avoir une idee ....
mais le plus gros a ete fait avec fdet_det_histo

comme je dois livrer ... je regarderais quand meme si j'ia le temps pour le sql dynamique .

[pb]Optimisation d'une procedure de suppression de doublons

Oracle

Discussions similaires

Partager

Partager