Requête récursive Oracle 11g

**Snipah** · 22/01/2013, 17h58

Bonjour à tous,

Je souhaiterais s'il vous plaît avoir vos avis et vos lumières sur une requête assez complexe, du moins pour moi.
Merci déjà aux personnes qui auront la patience de me lire, je m'aperçois après relecture que c'est assez touffu!

L'environnement est Oracle 11gR2. Je ne peux pas modifier le modèle de données, une appli de saisie/consultation se base directement dessus (pas de vues intermédiaires), donc je peux seulement ajouter des tables au besoin. (ce point reste peut être négociable avec l'appli si la modif est minime et vraiment bénéfique)

Je vais tacher d'expliquer brievement la problématique, en espérant être assez clair.
Je m'excuse de ne pas présenter tel quel le modèle, comme pour à peu près tout le monde en entreprise, ça m'est interdit.
Ceci dit je crois que c'est pas plus mal pour la compréhension

Départements :
t_departement (departement_id, nom_departement, activite_departement, site_departement)

Projets :
Un projet appartient à un seul département et peut être composé de sous-projets (auto-référencement).
On peut en théorie avoir jusqu'à 5 niveaux de sous-projet. Un projet principal a un parent_id null.
t_projet (projet_id, parent_projet_id, departement_id, reference_projet, code_projet, projet_type1_id, projet_type2_id, projet_type3_id)

Un projet est d'un certain type (qui va lui donner quelques attributs spécifiques), on a une sorte d'héritage mal modélisé (sur les trois clé étrangères dans t_projet deux seront forcément non renseignées, et un type_projet ne peut être référencé qu'une seule fois :/ )
t_projet_type1 (projet_type1_id, nom_projet_type1, ...)
t_projet_type2 (projet_type2_id, nom_projet_type2, ...)
t_projet_type3 (projet_type3_id, nom_projet_type3, ...)

Chaque projet_type a 0-n contrats rattachés, qui correspondent à un montant sur une période (qui peut être passée ou future) :
t_contrat_type1 (contrat_type1_id, projet_type1_id, debut_contrat_type1, fin_contrat_type1, montant_contrat_type1)
t_contrat_type2 (contrat_type2_id, projet_type2_id, debut_contrat_type2, fin_contrat_type2, montant_contrat_type2)
t_contrat_type3 (contrat_type3_id, projet_type3_id, debut_contrat_type3, fin_contrat_type3, montant_contrat_type3)

Chaque projet ou sous projet a un budget sur une période, les périodes pour un projet_id ne peuvent pas se chevaucher :
t_budget (t_budget_id, projet_id, debut_budget, fin_budget, montant_budget)

Une table calendrier (simplifiée) :
t_calendrier (jour)

On souhaite pouvoir fournir les indicateurs suivants à une date d donnée (présente, passée ou future) :
1) i1 : Dépense courante d'un projet ou sous-projet : c'est la somme des montant_contrat qui lui sont rattachés et qui sont actifs à cette date (debut_contrat <= d < fin_contrat)
2) i2 : Budget restant pour un projet : c'est la différence entre le montant_budget (actif à la date d) qui lui est rattaché et la dépense courante. Pour un sous-projet, ce sera le minimum entre son budget restant et celui de ses projets parents.
3) i3 : Dépense courante d'un projet ou sous-projet, sous projets inclus : c'est la somme des montant_contrat qui lui sont rattachés, ou qui sont rattachés à l'un de ces sous-projet, et qui sont actifs à cette date (debut_contrat <= d < fin_contrat)
4) i4 : Budget restant pour un projet, sous-projets inclus : c'est la différence entre le montant_budget qui lui est rattaché et la dépense courante de lui et tous ses sous-projets. Pour un sous-projet, ce sera le min entre son budget restant et celui de ses predecesseurs.

L'utilisateur doit saisir au minimum une date (ou une période, auquel cas on lui renverra la valeur pour chaque jour de cette période). Il peut aussi filtrer sur un pays, un département, un projet, une activité... classique quoi.
L'outil de restitution utilisé est BO.

Donc l'idée serait de sortir une (ou plusieurs) vue côté Oracle avec la structure suivante :
v_indicateurs (projet_id, jour, i1, i2, i3, i4)

Les volumétries à terme sont de l'ordre de 10 000 projets et 40 000 sous projets. En moyenne 15 contrats par projet.
Stocker tous les résultats à l'avance dans un modèle type datawarehouse parait compliqué, car les données sont susceptibles de changer tous les jours, il faudrait donc recalculer chaque jour tous les indicateurs pour tous les projets et toutes les dates du calendrier...
Je penche plutôt pour du calcul à la volée, mais j'ai quelques inquiétudes côté performances.

Suis-je passé à côté de quelque chose, auriez vous des idées quant à la solution à adopter?
Voici un début de requête, où i2 est incomplet car on doit pouvoir le comparer avec celui de ces predecesseur et ne garder que le plus petit. Sans parler de i3 et i4. Avez-vous une piste pour ce faire svp? Je me penche sur la récursivité, mais je ne suis pas très à l'aise avec et ne sais pas si j'arriverai à mes fins.

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
SELECT 
      p.projet_id,
      cal.jour,
      sum(coalesce(ct1.montant_contrat1, ct2.montant_contrat2, ct3.montant_contrat3,0)) AS i1,
      avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat1, ct2.montant_contrat2, ct3.montant_contrat3,0)) AS i2
FROM t_projet p
CROSS JOIN t_calendrier cal
LEFT JOIN t_budget b
     ON b.projet_id = p.projet_id
     AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
LEFT JOIN t_projet_type1 pt1
     JOIN t_contrat_type1 ct1
          ON pt1.projet_type1_id = ct1.projet_type1_id
  ON pt1.projet_type1_id = p.projet_type1_id
  AND cal.jour >= ct1.debut_contrat1 AND cal.jour < ct1.fin_contrat1
LEFT JOIN t_projet_type2 pt2
     JOIN t_contrat_type2 ct2
          ON pt2.projet_type2_id = ct2.projet_type2_id
  ON pt2.projet_type2_id = p.projet_type2_id
  AND cal.jour >= ct2.debut_contrat2 AND cal.jour < ct2.fin_contrat2
LEFT JOIN t_projet_type3 pt3
     JOIN t_contrat_type3 ct3
          ON pt3.projet_type3_id = ct3.projet_type3_id
  ON pt3.projet_type3_id = p.projet_type3_id
  AND cal.jour >= ct3.debut_contrat1 AND cal.jour < ct3.fin_contrat1
 
 
-- Version avec UNION
SELECT 
      p.projet_id,
      cal.jour,
      sum(coalesce(ct1.montant_contrat1,0)) AS i1,
      avg(coalesce(b.montant_budget,0))-sum(coalesce(ct1.montant_contrat1,0)) AS i2
FROM t_projet p
CROSS JOIN t_calendrier cal
LEFT JOIN t_budget b
     ON b.projet_id = p.projet_id
     AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
JOIN t_projet_type1 pt1
     ON pt1.projet_type1_id = p.projet_type1_id
LEFT JOIN t_contrat_type1 ct1
     ON pt1.projet_type1_id = ct1.projet_type1_id
     AND cal.jour >= ct1.debut_contrat1 AND cal.jour < ct1.fin_contrat1
UNION ALL     
SELECT 
      p.projet_id,
      cal.jour,
      sum(coalesce(ct2.montant_contrat2,0)) AS i1,
      avg(coalesce(b.montant_budget,0))-sum(coalesce(ct2.montant_contrat2,0)) AS i2
FROM t_projet p
CROSS JOIN t_calendrier cal
LEFT JOIN t_budget b
     ON b.projet_id = p.projet_id
     AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
JOIN t_projet_type2 pt2
     ON pt2.projet_type2_id = p.projet_type2_id
LEFT JOIN t_contrat_type2 ct2
     ON pt2.projet_type2_id = ct2.projet_type2_id
     AND cal.jour >= ct2.debut_contrat1 AND cal.jour < ct2.fin_contrat1
UNION ALL
SELECT 
      p.projet_id,
      cal.jour,
      sum(coalesce(ct3.montant_contrat3,0)) AS i1,
      avg(coalesce(b.montant_budget,0))-sum(coalesce(ct3.montant_contrat3,0)) AS i2
FROM t_projet p
CROSS JOIN t_calendrier cal
LEFT JOIN t_budget b
     ON b.projet_id = p.projet_id
     AND cal.jour BETWEEN b.debut_budget AND b.fin_budget
JOIN t_projet_type3 pt3
     ON pt3.projet_type3_id = p.projet_type3_id
LEFT JOIN t_contrat_type3 ct3
     ON pt3.projet_type3_id = ct3.projet_type3_id
     AND cal.jour >= ct3.debut_contrat1 AND cal.jour < ct3.fin_contrat1

Si y'a besoin de précisions supplémentaires n'hésitez pas, et merci à toute personne qui saura me donner un coup de main!