Piste d'audit dans un GROUP BY

[Fcapitaine]

Bonjour,

Je suis confronté à un problème de taille qui dépasse me semble t'il ma compétence. J’essaie de réaliser une piste d'audit dans un group by. Je m'explique :

- Je vais faire un sélection CRITERE_1, CRITERE_2,_CRITERE_3, sum(MNT) from TABLE1 group by CRITERE_1, CRITERE_2,_CRITERE_3

- J'aimerais faire un lien me permettant de faire un lien entre la ligne regroupée et le détail de ces lignes. Ce lien devrait se faire par un numéro de regroupement.

Exemple :
j'attribue un numéro unique pour la première ligne du regroupement par exemple 1001A et je voudrais toper dans un champ dédié l'ensemble des mouvement constituant le résultat dans ma table 1
si ma première ligne regroupe 1000 lignes dans ma table 1 alors je vais toper ces 1000 lignes avec la valeur 1001A, etc...

J'ai bien pensé à des fonction comme GROUP BY CUBE mais je suis à court d'idée...
J'ai créé une boucle mais le temps de traitement est de 10heures...

Avez vous des pistes ??

Merci à vous

rudy FIEVET

[tedo01]

Bonjour,

La fonction DENSE_RANK() va te permettre d'obtenir cette numérotation :

Code :

1
2
3
4
5
6
SELECT critere_1
     , critere_2
     , critere_3
     , mnt
     , dense_rank() over (ORDER BY critere_1, critere_2, critere_3 ) flag
FROM table1

Après, le plus simple est de créer une autre table à partir de ce select...

[Fcapitaine]

Merci à toi tedo01.

Sais tu si nativement, je peux faire commencer la numérotation par un autre chiffre qui serait stocké quelque part ???

[tedo01]

Re-bonjour,
Pour faire commencer à 12 :

Code :

1
2
3
4
5
6
SELECT critere_1
     , critere_2
     , critere_3
     , mnt
     , 11 + dense_rank() over (ORDER BY critere_1, critere_2, critere_3 ) flag
FROM table1

[Fcapitaine]

Encore un grand merci à toi !!!!!

[Fcapitaine]

Re bonjour,

Ta solution fonctionne parfaitement mais le temps de traitement est trés long.
Environ 2 heures pour 2.5 millions de lignes.

Le traitement est décomposé en deux phases :
- Une vue qui va faire le critère de regroupement
- Un update dans une table pour conserver la piste d'audit

Voilà le code de la vue initiale
1. VUE

Code :

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
 
CREATE OR REPLACE VIEW SUNAUXF.V_JACMOVP_PREALIMENTATION_ANA AS
SELECT 
SJ.WNOPE,
Sj.Socmv,
Sj.Journ,
Sj.Dcpmv,
Sj.Cimcp,
Sj.Indcmv,
Sj.Dvtmv,
Case When Vpc.Reg_Critere_01 = 1 Then Sj.F_Code_Prod        Else '*'      End AS F_Code_Prod,
Case When Vpc.Reg_Critere_02 = 1 Then Sj.F_Cat_Min          Else '*'      End AS F_Cat_Min,
Case When Vpc.Reg_Critere_03 = 1 Then Sj.F_Exo_Surv         Else '*'      End AS F_Exo_Surv,
Case When Vpc.Reg_Critere_08 = 1 Then Sj.F_Tiers            Else '*'      End AS F_Tiers,
Case When Vpc.Reg_Critere_11 = 1 Then Sj.Numlot             Else 0        End AS Numlot,
Case When Vpc.Reg_Critere_12 = 1 Then Sj.Numchq             Else '*'      End AS Numchq,
Case When Vpc.Reg_Critere_13 = 1 Then Sj.Wenca              Else 0        End AS Wenca,
Case When Vpc.Reg_Critere_14 = 1 Then Sj.Wnrgt              Else 0        End AS Wnrgt,
Case When Vpc.Reg_Critere_15 = 1 Then Sj.Wnuco              Else 0        End AS Wnuco,
Case When Vpc.Reg_Critere_16 = 1 Then Sj.Mrgmv              Else '*'      End AS Mrgmv,
Case When Vpc.Reg_Critere_17 = 1 Then Sj.F_Indic_Afn_Renou  Else '*'      End AS F_Indic_Afn_Renou,
Case When Vpc.Reg_Critere_18 = 1 Then Sj.Decvm              Else 0        End AS Decvm,
Case When Vpc.Pointableecr=1 AND Vpc.Typepointage=0   Then SJ.Code_Interbank             Else '*'        End AS Code_Interbank,
Dense_Rank() Over ( ORDER BY 
F_Code_Prod,
F_Cat_Min,
F_Exo_Surv,
F_Tiers,
Numlot,
Numchq,
Wenca,
Wnrgt,
Wnuco,
Mrgmv,
Decvm,
F_Indic_Afn_Renou,
Decvm
) Flag
FROM Sunauxf.V_Jacmovp_Notinteg Sj,
P2_Test.V_Plancompte@Inst92sage Vpc 
WHERE 
(Substr(Sj.Cimcp,1,9) = Vpc.Codecompte
AND Vpc.Caption = Decode(Sj.Socmv,10,'PC Assurance',80,'PC Assurance',90,'PC Général')
AND Nvl(Vpc.Code,'*') = Nvl(Sj.Code_Interbank,'*'))
;

2. UPDATE

Code :

1
2
3
4
5
6
7
8
 
UPDATE Sunauxf.Jacmovp_Intsage A
 
SET
 
A.Numero_Reg_A = (
 
SELECT B.Flag FROM Sunauxf.V_Jacmovp_Prealimentation_Ana B WHERE A.Wnope = B.Wnope)

Qu'en penses tu ?
Pourrais je optimiser ma requête? je m'y prends mal ?

[tedo01]

Bonsoir,
Le problème, c'est que pour l'update, Oracle parcourt le select de la vue pour chaque ligne de la table...

Pour gagner du temps, il faudrait

1. Créer une table temporaire avec le select de la vue et les données de la table initiale :
   Code :

   CREATE TABLE tmp AS SELECT ...

2. Re-créer la table initiale après l'avoir vidée, en faisant un
   Code :

   INSERT INTO ma_table SELECT ... FROM tmp

Après, il faut encore voir les index qui pourraient exister sur la table, ils peuvent ralentir les INSERT.

[Fcapitaine]

Je savais pas que oracle devait balayer toute la table avant l'update.
Par contre je ne peux pas vraiment toucher à la table JACMOVP_INTSAGE

Mais je peux créer une table temporaire avec le RankDense()
pour après faire l'update

Tu penses que cela peut accélérer les choses ?

[tedo01]

Bonjour,
Les performances dépendent de beaucoup de critères, je pense que le mieux c'est d'essayer :-)