traitement d'insertion récursif

**buzzzy** · 08/08/2011, 12h33

Bonjour,

voilà je suis confronté à une problématique que je n'arrive pas à résoudre et que vous expose :
j'ai 6 tables (TI_EDI_CPT_DIR_AG_COL1,TI_EDI_CPT_DIR_AG_COL2,..... à TI_EDI_CPT_DIR_AG_COL6) en source qui ont la même structure :

Code :

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

1
2
3
4
5
6
7
8
9
10
COL1		VARCHAR2(255 BYTE)
COL2		VARCHAR2(255 BYTE)
COL3		VARCHAR2(255 BYTE)
LB_TIE		VARCHAR2(255 BYTE)
LB_D_EN_REL	VARCHAR2(255 BYTE)
LB_ACT		VARCHAR2(255 BYTE)
LB_MTF_SEL	VARCHAR2(255 BYTE)
LB_D_ETRN_EPA	VARCHAR2(255 BYTE)
LB_D_MAJ_ETRN_EPA VARCHAR2(255 BYTE)
LB_RESU_ANA	VARCHAR2(255 BYTE)

Je dois insérer/mixer les données de mes 6 tables source vers une table cible dans un certain ordre :
1/ une ligne vide (caractérisée par le code 2 (champ "Col1").
2/ une ligne de libellés (caractérisée par le code 1 (champ "Col1")
3/ une ligne vide (caractérisée par le code 2 (champ "Col1")
4/ une ligne comportant le nom de l'agent (caractérisée par le code 3 (champ "Col1")
5/ une ligne vide (caractérisée par le code 2 (champ "Col1").
5/ une ligne de libellés (caractérisée par le code 4 (champ "Col1")
6/ une ou plusieurs lignes de données (caractérisée par le code 5 (champ "Col1"). S'il y a plusieurs lignes, la 1ere ligne commence par le code 5, les suivantes par le code 6.
7/ une ligne de données (caractérisée par le code 6 (champ "Col1")
8/ une ligne vide (caractérisée par le code 2 (champ "Col1")

Voici le contenu de mes tables source :
1/

Code :

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

1
2
3
4
5
6
7
TI_EDI_CPT_DIR_AG_COL1 (correspond à une ligne de libellés, COl1=1)
COl1    Col2          Col3      LB_TIE          LB_D_EN_REL                   LB_ACT   LB_MTF_SEL   LB_D_ETRN_EPA    LB_D_MAJ_ETRN_EPA   LB_RESU_ANA
1	104682240		CONTROLE 	COMPTES A  EXAMINER NOV2010					
1	104682245		CONTROLE 	COMPTES A  EXAMINER NOV2010					
1	104682246		CONTROLE 	COMPTES A  EXAMINER NOV2010					
1	104682247		CONTROLE 	COMPTES A  EXAMINER NOV2010					
1	300762019		CONTROLE 	COMPTES A  EXAMINER NOV2010

2/

Code :

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

1
2
3
4
5
6
7
TI_EDI_CPT_DIR_AG_COL2 (correspond à une ligne vide, COl1=2)
COl1    Col2          Col3      LB_TIE          LB_D_EN_REL                   LB_ACT   LB_MTF_SEL   LB_D_ETRN_EPA    LB_D_MAJ_ETRN_EPA   LB_RESU_ANA
2	104682240								
2	104682245								
2	104682246								
2	104682247								
2	300762019

3/

Code :

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

1
2
3
4
5
6
7
TI_EDI_CPT_DIR_AG_COL3 (correspond au nom de l'affectataire (=agent), COl1=3 avec son ID associée (Col3=ID agent))
COl1    Col2          Col3      LB_TIE                              LB_D_EN_REL                   LB_ACT   LB_MTF_SEL   LB_D_ETRN_EPA    LB_D_MAJ_ETRN_EPA   LB_RESU_ANA
3	104682240	7140	AFFECTATAIRE : FRANCOIS
3	104682240	35256	AFFECTATAIRE : FRED
3	104682240	7224	AFFECTATAIRE : FABRICE
3	104682240	7264	AFFECTATAIRE : FABIEN
...

...

4/

Code :

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

1
2
3
4
5
6
7
TI_EDI_CPT_DIR_AG_COL4 (correspond au lignes de libellés (Col1=4))
COl1    Col2          Col3      LB_TIE              LB_D_EN_REL             LB_ACT          LB_MTF_SEL                              LB_D_ETRN_EPA           LB_D_MAJ_ETRN_EPA       LB_RESU_ANA
4	104682240		CLIENT CONCERNE	    ENTREE EN RELATION	ACTIVITE	OPERATIONS MOTIVANT LA SELECTION	DATE DE L'ENTRETIEN	DATE DE L'ENTRETIEN	RESULTAT DE L'EXAMEN
4	104682245		CLIENT CONCERNE	    ENTREE EN RELATION	ACTIVITE	OPERATIONS MOTIVANT LA SELECTION	DATE DE L'ENTRETIEN	DATE DE L'ENTRETIEN	RESULTAT DE L'EXAMEN
4	104682246		CLIENT CONCERNE	    ENTREE EN RELATION	ACTIVITE	OPERATIONS MOTIVANT LA SELECTION	DATE DE L'ENTRETIEN	DATE DE L'ENTRETIEN	RESULTAT DE L'EXAMEN
4	104682247		CLIENT CONCERNE	    ENTREE EN RELATION	ACTIVITE	OPERATIONS MOTIVANT LA SELECTION	DATE DE L'ENTRETIEN	DATE DE L'ENTRETIEN	RESULTAT DE L'EXAMEN
4	300762019		CLIENT CONCERNE	    ENTREE EN RELATION	ACTIVITE	OPERATIONS MOTIVANT LA SELECTION	DATE DE L'ENTRETIEN	DATE DE L'ENTRETIEN	RESULTAT DE L'EXAMEN

5/

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
TI_EDI_CPT_DIR_AG_COL5 (correspond au lignes de données (Col1=5)) (Col3=ID agent)
COl1    Col2          Col3      LB_TIE                                  LB_D_EN_REL             LB_ACT                  LB_MTF_SEL                              LB_D_ETRN_EPA           LB_D_MAJ_ETRN_EPA       LB_RESU_ANA
5	104682240	7239	2240 XXXXXX X X MONSIEUR TOTO   	10-OCT-97	employe du prive         	1 Emissions de chèques pour 7900€			
5	104682240	7239	2240 XXXXXX X X MONSIEUR TOTO   	10-OCT-97	employe du prive         	8 Emissions de chèques pour 6900€			
5	104682240	7239	2240 XXXXXX X X MONSIEUR TOTO   	10-OCT-97	employe du prive         	2 Virements de chèques pour 13000€			
5	104682240	7239	2240 XXXXXX X X MONSIEUR TOTO   	10-OCT-97	employe du prive         	1 Virement de chèques pour 1659,79€			
5	104682240	7224	2240 XXXXXX X X MONSIEUR TATA		04-NOV-97	employe du public        	7 Virements reçus pour 1788.92€			
5	104682240	7224	2240 XXXXXX X X MONSIEUR TATA		04-NOV-97	employe du public        	1 Remises de chèques pour 50€			
5	104682240	7224	2240 XXXXXX X X MONSIEUR TATA		04-NOV-97	employe du public        	3 Virements reçus pour 121.14€			
5	104682240	7224	2240 XXXXXX X X MONSIEUR TATA		04-NOV-97	employe du public        	17 Emissions de chèques pour 826.3€			
5	104682240	7224	2240 XXXXXX X X MONSIEUR TATA		04-NOV-97	employe du public        	2 Virements émis pour 100€			
5	104682240	7224	2240 XXXXXX X X MONSIEUR TATA		04-NOV-97	employe du public        	2 Virements émis pour 100€			
5	104682240	7224	2240 XXXXXX X X MONSIEUR TATA		04-NOV-97	employe du public        	7 Prélevements payés pour 766.52€			
...
...

6/

Code :

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

1
2
3
4
5
6
7
TI_EDI_CPT_DIR_AG_COL6 (correspond au lignes de données (Col1=6))
COl1    Col2          Col3      LB_TIE                                  LB_D_EN_REL             LB_ACT                  LB_MTF_SEL                              LB_D_ETRN_EPA           LB_D_MAJ_ETRN_EPA       LB_RESU_ANA
6	104682240		2240 XXXXXX X X MONSIEUR TOTO		Jamais signalé		Jamais topé				
6	104682240		2240 XXXXXX X X MONSIEUR TATA		Jamais signalé		Jamais topé				
6	104682240		2240 XXXXXX X X MONSIEUR TITI 		Jamais signalé		Jamais topé				
...
...

La table cible doit ressembler à ça :

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
2	104682240		                                             	          	                                	                                                  
1	104682240		            CONTROLE                     	COMPTES A 	EXAMINER 10 2010                	                                                  
2	104682240	                                             	          	                                	                                                  
3	104682240		AFFECTATAIRE : FRANCOIS                      	          	                                	                                                  
2	104682240		                                             	          	                                	                                                  
4	104682240		          CLIENT CONCERNE                    	DATE RELATION 	ACTIVITE            	         OPERATIONS MOTIVANT LA SELECTION         
5	104682240		02324 XXXXX XXXX XX TOTO                	07/02/1994	PHOTOCOPIE                       	13 REMISES CHEQUES POUR 734417.46 EURO            
6	104682240		JAMAIS SIGNALE                               	          	JAMAIS TOPE                     	249 VIREMENTS RECUS POUR 463486.57 EURO           
6	104682240		                                             	          	                                	2 EMISS. CHQ MONTANT ROND POUR 400000 EURO        
2	104682240		                                             	          	                                	                                                  
3	104682240		AFFECTATAIRE : FRED                       	          	                                	                                                  
2	104682240		                                             	          	                                	                                                  
4	104682240		          CLIENT CONCERNE                    	DATE RELATION 	ACTIVITE            	         OPERATIONS MOTIVANT LA SELECTION         
5	104682240		02324 XXXXXX XXXX XX TATA                     	05/11/2009	TRAVAUX D INSTALLATION           	9 REMISES CHEQUES POUR 43790.13 EURO              
6	104682240		JAMAIS SIGNALE                               	          	JAMAIS TOPE                     	1 TRANSFERTS IMPORT POUR 36138.19 EURO            
2	104682245		                                             	          	                                	                                                  
1	104682245		            CONTROLE                    	COMPTES A 	EXAMINER 10 2010                	                                                  
2	104682245		                                             	          	                                	                                                  
3	104682245		AFFECTATAIRE : FABRICE                              	          	                                	                                                  
2	104682245		                                             	          	                                	                                                  
4	104682245		          CLIENT CONCERNE                    	DATE RELATION   ACTIVITE            	         OPERATIONS MOTIVANT LA SELECTION         
5	104682245		02324 XXXXXX XXXX XX TETE          	        06/09/1996 .	ELECTRICIEN                             249 VIREMENTS RECUS POUR 463486.57 EURO                                                 
6	104682245		                                             	          	                                	                                                  
2	104682246		                                             	          	                                	                                                  
1	104682246		            CONTROLE                     	COMPTES A 	EXAMINER 10 2010                	                                                  
2	104682246		                                             	          	                                	                                                  
3	104682246		AFFECTATAIRE : FABIEN 
2	104682246		                                             	          	                                	                                                  
4	104682246		          CLIENT CONCERNE                    	 DATE RELATION  ACTIVITE            	         OPERATIONS MOTIVANT LA SELECTION         
5	104682246		02137 XXXXXX XXXX XX TITI                	28/10/1996	PLOMBIER                        	18 VIREMENTS RECUS POUR 227018.79 EURO            
6	104682246		JAMAIS SIGNALE                               	          	JAMAIS TOPE                     	1 TRANSFERTS EXPORT POUR 35974.5 EURO             
2	104682246		                                             	          	                                	                                                  
3	104682246		AFFECTATAIRE : PHILIPPE                       	          	                                	                                                  
2	104682246		                                             	          	                                	                                                  
4	104682246		          CLIENT CONCERNE                    	 DATE RELATION 	ACTIVITE            	         OPERATIONS MOTIVANT LA SELECTION         
5	104682246		02137 XXXXXX XXXX XX TUTU                 	09/05/2007	EDITION                          	2 REMISES CHEQUES POUR 53724.32 EURO              
6	104682246		JAMAIS SIGNALE                               	          	TOPE EN 200810                  	1 TRANSFERTS EXPORT POUR 42825.61 EURO            
2	104682246		                                             	          	                                	                                                  
...

La requête que j'ai créée ne me satisfait pas :
select LST.* from (
select * from ti_edi_cpt_dir_ag_col1
union all
select * from ti_edi_cpt_dir_ag_col2
union all
select * from ti_edi_cpt_dir_ag_col3
union all
select * from ti_edi_cpt_dir_ag_col4
union all
select * from ti_edi_cpt_dir_ag_col5
union all
select * from ti_edi_cpt_dir_ag_col6) LST, TI_EDI_LIG_ORDER ORD
WHERE LST.COL1 = ORD.LIG
order by LST.COL2, ORD.TYP, ORD.ORD

avec la table TI_EDI_LIG_ORDER qui a la structure :

Code :

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

1
2
3
4
5
6
7
8
9
10
TYP     LIG     ORD
1	2	1
1	1	2
1	2	3
2	3	1
2	2	2
2	4	3
2	5	4
2	6	5
2	2	6

J'avoue que je n'ai pas d'idée...
Merci d'avance pour votre aide

**orafrance** · 08/08/2011, 13h52

Envoyé par buzzzy

Je dois insérer/mixer les données de mes 6 tables source vers une table cible dans un certain ordre

Le contenu d'une table n'est pas ordonnée, donc insérer dans un certain ordre ça rime à rien, au mieux il faudra ajouté une colonne qui donne un numéro d'ordre à tes lignes via une séquence par exemple.

**Mohamed.Houri** · 08/08/2011, 14h05

Envoyé par orafrance

Le contenu d'une table n'est pas ordonnée, donc insérer dans un certain ordre ça rime à rien, au mieux il faudra ajouté une colonne qui donne un numéro d'ordre à tes lignes via une séquence par exemple.

C'est archi vrai.

Sauf qu'il peut nous dire qu'il veut faire cela pour générer une certaine valeur du clustering Factor d'un certain index qu'il a l'intention de créér.

Mais vu la question, je ne pense pas qu'il ait pensé à cela

Bien Cordialement

Mohamed Houri

**patgabjoe** · 08/08/2011, 16h24

Allez on va aider le collègue.

Pour résumer, il doit faire un état BO et veut constituer un état qui ressemblera à sa table cible.
Cet état aura :

1 Titre : ce sont les 3 premières lignes, composées de 2 lignes vides avec un centre une ligne pour le titre "Controle Comptes a examiner ..."

Et des blocs répétitifs

Mon idée est donc de définir des groupes.

Comme vous pouvez le voir, on peut constituer 3 groupes.

Groupe 1 : "AFFECTATAIRE"
Groupe 2 : "Clients Concernés"
Groupe 3 : "Information Client"

Dans l'état on aura :

La liste de tous les affectataires avec les clients qui leur sont affectés

Donc :

N Groupes d'affectataires pour 1 Etat
N Groupes de clients concernés pour 1 Affectataire
N Lignes de libellés (actuellement 2) pour 1 Client

Je pensais donc faire cela via le paramétrage d'une table ...

Code :

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

1
2
3
G1 contient n G2
G2 contient n G3
G3 contient n lignes

Vous voyez le genre ?

Et peut-être par la suite utiliser des fonctions analytiques oracle ...

Si qqun à une piste ...

Ai-je bien expliqué ton cas le collègue ?

**laurentschneider** · 09/08/2011, 10h29

Envoyé par orafrance

Le contenu d'une table n'est pas ordonnée, donc insérer dans un certain ordre ça rime à rien...

juste histoire de trouver l'exception qui confirme la régle...

CREATE INDEX
You can specify NOSORT to indicate to the database that the rows are already stored in the database in ascending order... For greatest savings of sort time and space, use this clause immediately after the initial load of rows into a table

demo

Code :

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

1
2
3
4
5
create table t1 as 
  with t as (select * from dual connect by level<=3000) 
  select rownum x from t,t;
create table t2 as 
  select * from t1 order by dbms_random.value;

les tables t1 et t2 sont identiques. T1 est triée. T2 ne l'est pas

Code :

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

1
2
3
4
5
6
SQL> create index i1 on t1(x) nosort
Index created.
Elapsed: 00:00:06.26
SQL> create index i2 on t2(x)
Index created.
Elapsed: 00:00:30.40

création 5 fois plus rapide!

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> select count(*) from t1 where x between 100 and 200
 
  COUNT(*)
----------
       101
1 row selected.
Elapsed: 00:00:00.12
SQL> select count(*) from t2 where x between 100 and 200
 
  COUNT(*)
----------
       101
1 row selected.
Elapsed: 00:00:01.06

selection également 5x plus rapide !!!

**Mohamed.Houri** · 09/08/2011, 11h35

Je pense que ce qu'il faut inculquer ici, c'est que ca n'est pas parce que nous avons trié les données dans le select qui a servi à l'insert que ces dernières vont être forcément rassemblées ensemble au même endroit et ordonnées. Oracle va insérer les données là où il trouvera de l'espace libre.Je ne vous parle pas de l'effet d'un simple delete qui suivra l'insert "ordonné"

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
 
mhouri.world > CREATE TABLE t1 AS
  2    WITH t AS (SELECT * FROM dual connect BY level<=30)
  3    select rownum x from t,t;
 
Table created.
 
mhouri.world > select
  2        substr(rowid,10,6) bloc
  3       ,substr(rowid,16,3) rec
  4       ,x
  5  from t1;
 
BLOC   REC          X                                                           
------ --- ----------                                                           
AAAAN8 AAA          1                                                           
AAAAN8 AAB          2                                                           
AAAAN8 AAC          3                                                           
AAAAN8 AAD          4                                                           
AAAAN8 AAE          5                                                           
AAAAN8 AAF          6                                                           
AAAAN8 AAG          7                                                           
AAAAN8 AAH          8                                                           
AAAAN8 AAI          9                                                           
AAAAN8 AAJ         10                                                           
AAAAN8 AAK         11                                                           
 
BLOC   REC          X                                                           
------ --- ----------                                                           
AAAAN8 AAL         12                                                           
AAAAN8 AAM         13                                                           
AAAAN8 AAN         14                                                           
AAAAN8 AAO         15                                                           
AAAAN8 AAP         16                                                           
AAAAN8 AAQ         17                                                           
AAAAN8 AAR         18                                                           
AAAAN8 AAS         19

Remarquez comment les données sont parfaitement triés (AAA,AAB, AAC,etc..). Mais voyons ce que peut faire un simple delete

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
 
 
mhouri.world > delete from t1 where x = 10;
 
1 row deleted.
 
mhouri.world > insert into t1 values (10);
 
1 row created.
 
mhouri.world > commit;
 
Commit complete.
 
mhouri.world >select
  2        substr(rowid,10,6)  bloc
  3       ,substr(rowid,16,3) rec
  4       ,x
  5  from t1;
 
BLOC   REC          X                                                           
------ --- ----------                                                           
AAAAN8 AAA          1                                                           
AAAAN8 AAB          2                                                           
AAAAN8 AAC          3                                                           
AAAAN8 AAD          4                                                           
AAAAN8 AAE          5                                                           
AAAAN8 AAF          6                                                           
AAAAN8 AAG          7                                                           
AAAAN8 AAH          8                                                           
AAAAN8 AAI          9                                                           
AAAAN8 AAK         11                                                           
AAAAN8 AAL         12                                                           
 
BLOC   REC          X                                                           
------ --- ----------                                                           
AAAAN8 AAM         13                                                           
AAAAN8 AAN         14                                                           
AAAAN8 AAO         15                                                           
AAAAN8 AAP         16                                                           
AAAAN8 AAQ         17                                                           
AAAAN8 AAR         18                                                           
AAAAN8 AAS         19

L'ordre est déjà brisé (AAH, AAI, AAK, etc..)

Bien Cordialement

Mohamed Houri

**laurentschneider** · 09/08/2011, 12h25

c'est tout simple, si tu fais un CREATE INDEX NOSORT et que la table n'est pas triée, le CREATE INDEX plante...

Code :

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

1
2
3
4
5
6
SQL> drop index i2
Index dropped.
SQL> create index i2 on t2(x) nosort
create index i2 on t2(x) nosort
Error at line 3
ORA-01409: NOSORT option may not be used; rows are not in ascending order

**patgabjoe** · 09/08/2011, 13h41

Bonjour,

Mais j'ai comme l'impression que l'on s'éloigne du sujet initial.

Pour résumer, est-ce que vous voyez comment avec les fonctions analytiques d'Oracle on peut lister les données en groupe.

G1
G2
G3 (2 lignes)
G3 (2 lignes)
G3 (2 lignes)
G2
G3 (2 lignes)
G3 (2 lignes)

**patgabjoe** · 09/08/2011, 13h49

Bonjour,

Mais j'ai comme l'impression que l'on s'éloigne du sujet initial.

Pour résumer, est-ce que vous voyez comment, avec les fonctions analytiques d'Oracle (ou peut-être un autre moyen), on peut lister les données en groupe.

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
G1
    G2
         G3 (2 lignes)
         G3 (2 lignes)
         G3 (2 lignes)
   G2
         G3 (2 lignes)
         G3 (2 lignes)
   G2
         G3 (2 lignes)
         G3 (2 lignes)
         G3 (2 lignes)
         G3 (2 lignes)
G1
    G2
         G3 (2 lignes)
   G2
         G3 (2 lignes)
         G3 (2 lignes)
 
G1
    G2
         G3 (2 lignes)
         G3 (2 lignes)
         G3 (2 lignes)
   G2
         G3 (2 lignes)
   G2
         G3 (2 lignes)

etc ....

Pour rappel 1 groupe peut avoir plusieurs lignes, et chaque type de ligne est identifié par 1 code (nombre) en première position (COL1)
Actuellement 1 Table = 1 Type de ligne ... mais je pense qu'il est préférable de fusionner les tables avant de faire le traitement.

Les experts vous en pensez quoi ?
Merci

**orafrance** · 09/08/2011, 15h27

Envoyé par laurentschneider

CREATE INDEX
You can specify NOSORT to indicate to the database that the rows are already stored in the database in ascending order... For greatest savings of sort time and space, use this clause immediately after the initial load of rows into a table

C'est beau

Ceci étant, impossible d'intercaler des lignes donc dans le cas pratique présent ça me parait difficile à moins de recréer la table à chaque fois... enfin, si j'ai bien compris

**laurentschneider** · 10/08/2011, 15h57

absolument, c'est le genre de truc inutilisable qu'on employe jamais, c'était juste pour le fun

traitement d'insertion récursif

Oracle

Discussions similaires

Partager

Partager