Discussion :

[Ntotor]

Bonjour petite bouteille à la mer pour une problématique assez simple :

Dans une table j'ai des enregistrements de ce type :
[Code], [Date de début], [Date de fin]

Actuellement il existe une vue qui permet de retourner ces enregistrements par année couverte cad :

si on a [A01],[15/10/2012],[16/01/2014], la vue renverra 3 lignes:

[A01],[15/10/2012],[31/12/2012]
[A01],[01/01/2013],[31/12/2013]
[A01],[01/01/2014],[16/01/2014]

Le problème c'est que cette vue est actuellement très gourmande en ressource (Jointure avec une table calendrier pour obtenir le résultat).

Est ce que quelqu'un aurait une idée pour simplifier ce fonctionnement ? Une formule magique avec un simple select sans passer par la case jointure ?

Merci d'avance et joyeuse fête à tous !

[Waldar]

Il faudrait le code de la vue ! Vous pouvez modifier les noms de tables / colonnes tant que vous conservez la logique.
Edit : j'ai regardé un peu vite, vous voulez séparer votre intervalle de date par année, pas besoin du code.

[Waldar]

Ceci devrait faire l'affaire :

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
with cte_matable as
(
select 'A01' as code
     , date '2012-10-15' as dt_deb
     , date '2014-11-16' as dt_fin
  from dual
 union all
select 'A02'
     , date '2016-01-02'
     , date '2016-12-30'
  from dual
 union all
select 'A03'
     , date '2015-06-05'
     , date '2014-06-01'
  from dual
)
    select code
         , greatest(dt_deb, trunc(dt_deb, 'yyyy') + (level - 1) * interval '1' year) as dt_deb
         , least(dt_fin, trunc(dt_fin, 'yyyy') + (level - trunc(months_between(trunc(dt_fin, 'yyyy'), trunc(dt_deb, 'yyyy'))/12)) * interval '1' year - 1) as dt_fin
      from cte_matable
connect by level <= 1 + trunc(months_between(trunc(dt_fin, 'yyyy'), trunc(dt_deb, 'yyyy'))/12)
       and prior code = code
       and prior dbms_random.value is not null;

Edit : j'ai ajouté une correction sur le calcul de la date de fin, et pris en compte que vous pouvez avoir plusieurs lignes en entrée.

[Ntotor]

Un très grand merci, ça marche nickel !

Il me manquait la notion de connect by level, de même pour l'interval : c'est très instructif ! Il va me falloir un peu de temps pour intégrer tout ça mais ça m'ouvre pas mal de perspectives

Encore un grand grand merci !
... pis encore un pour la route ... merci !

Bonne fêtes de fin d'années !

[Ntotor]

Bon je me suis avancé un peu en pensant que c’était résolu mais c'est de ma faute j'avais simplifié le problème (et effectivement la première problématique est résolu) :

Ma table est plutôt de la forme (toujours simplifié pour faciliter la lecture)
[Code 1], [Code Retour 1], [Date de début], [Date de fin]

l'appel de la vue se fait via
select * from ma_vue where Code1 = 'Mon code';

Avec l'ancienne écriture de la vue (avec jointure sur une table calendrier), j'ai un coût de 90 quand je regarde le plan d’exécution, l'index de la table source est utilisé.

Avec la nouvelle écriture la table source est lue entièrement : l'index n'est pas utilisé et le coût passe à 450.

Voici l'implémentation du code donné précédemment:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
create or replace view V_MaTable As 
  SELECT Code1 AS Code1,
    greatest(DATEDEBUT, trunc(DATEDEBUT, 'yyyy') + (level - 1) * interval '1' year) AS DATEDEBUT,
    least(DATEFIN-1, trunc(DATEFIN-1, 'yyyy') + (level - trunc(months_between(trunc(DATEFIN-1, 'yyyy'), trunc(DATEFIN-1, 'yyyy'))/12)) * interval '1' year - 1) as DATEFIN,
    CodeRetour1      AS CodeRetour1
  FROM MaTable
connect by level <= 1 + trunc(months_between(trunc(DATEFIN-1, 'yyyy'), trunc(DATEDEBUT, 'yyyy'))/12)
       and prior Code1 = Code1
       and prior CodeRetour1 = CodeRetour1
       and prior dbms_random.value is not null;
 
 
select * from V_MaTable where Code1 = 'HISTO_01' ;

Donc ma question : Est ce que vous pensez qu'il est possible d'utiliser cette méthode tout en utilisant l'index de la table ?

Merci à nouveau

[Waldar]

Peut-être, peut-être pas.
Essayez de le forcer avec un hint :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
create or replace view v_matable (Code1, DateDebut, DateFin, CodeRetour1) as
    select /*+ index(<matable> <nom_index>) */
           code1
         , greatest(datedebut, trunc(datedebut, 'yyyy') + (level - 1) * interval '1' year)
         , least(datefin-1, trunc(datefin-1, 'yyyy') + (level - trunc(months_between(trunc(datefin-1, 'yyyy'), trunc(datefin-1, 'yyyy'))/12)) * interval '1' year - 1)
         , coderetour1
      from matable
connect by level                   <= 1 + trunc(months_between(trunc(datefin-1, 'yyyy'), trunc(datedebut, 'yyyy'))/12)
       and prior code1              = code1
       and prior coderetour1        = coderetour1
       and prior dbms_random.value is not null;