Retourner interval de date

Version imprimable

18/12/2017, 09h30
Ntotor

Retourner interval de date

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 !
18/12/2017, 09h59
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.

Ceci devrait faire l'affaire :

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

18/12/2017, 14h41
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 !

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:

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

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

Code:

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;

18/12/2017, 16h31
Waldar

Ou si vous recherchez toujours un code à la fois, vous pouvez créer une fonction qui prendra en entrée ce code.

Bonjour,

Bon je n'ai pas réussi à utilisé l'index via la vue par contre en sortant de la vue et en modifiant le from ça marche :), et vu le gain de perf ça vaux le coup, je passe d'un coût d'origine de 90 à 17 au final ...
Code:

1 2 3 4 5 6 7 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 (select * from cte_matable where code = 'Mon code') 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;
Un grand merci,
Et a nouveau de bonne fête de fin d'année !