Piste d'audit dans un GROUP BY

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

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...

Merci à toi tedo01.

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

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

---

:)

Encore un grand merci à toi !!!!!
:ccool::ccool::ccool:

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 ?

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.

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 ?

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