Utilisation du connect by level

Version imprimable

Bonsoir,

J'ai un bout de code SQL que je souhaite optimiser.

Voici la table source A:
Code:

1 2 3 Pk date_debut date_fin 1 06.05.2012 02.03.2013 2 02.09.2012 06.10.2012
Je souhaite 'multiplier' les enregistrements selon le nb de mois-années, dans la table B:
Code:

1 2 3 4 5 6 7 Pk FK Mois Annee 1 1 05 2012 2 1 06 2012 etc. 3 1 03 2013 4 2 09 2013 5 2 10 2013
Le code qui marche mais qui est très long est :
Code:

1 2 3 4 5 6 Select distinct Pk ,extract(month from add_months(date_debut, level-1)) as MONTH ,extract(yeaR from add_months(date_debut, level-1)) as YEAR from (SELECT * FROM A) connect by level <= TRUNC(months_between (date_fin,date_debut) ) +1
Je sent que ce code est optimisable car je n'aime pas les distinct et que pour une ligne (where Pk=1) ca va très vite, alors que la ca cross join les mois année n fois... puis le distinct néttoie péniblement.

Avez-vous une idée pour éviter ce distinct ?

21/11/2012, 21h27
Waldar

Utilisez un calendrier, avec une jointure "between" ça ira tout seul.

Un calendrier c'est toujours super utile, mais sinon l'équivalent sans DISTINCT :

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
SQL> with A as (
select 1 as PK, to_date('06.05.2012','dd.mm.yyyy') as date_debut, to_date('02.03.2013','dd.mm.yyyy') as date_fin from dual union all
select 2      , to_date('02.09.2012','dd.mm.yyyy')              , to_date('06.10.2012','dd.mm.yyyy')             from dual
)
SELECT rownum as PK, t.PK as FK, 
       extract(month FROM add_months(t.date_debut, to_number(x.column_value)-1)) AS "MONTH",
       extract(year  FROM add_months(t.date_debut, to_number(x.column_value)-1)) AS "YEAR"
  FROM A t
 CROSS JOIN TABLE(
              cast(multiset(
                      SELECT level
                        FROM dual
                     connect BY level <= trunc(months_between (date_fin,date_debut))+1
                           ) AS sys.odcivarchar2list 
                   )
                 ) x
/
 
        PK         FK      MONTH       YEAR
---------- ---------- ---------- ----------
         1          1          5       2012
         2          1          6       2012
         3          1          7       2012
         4          1          8       2012
         5          1          9       2012
         6          1         10       2012
         7          1         11       2012
         8          1         12       2012
         9          1          1       2013
        10          1          2       2013
        11          2          9       2012
        12          2         10       2012
 
12 rows selected.
 
SQL>

Mais attention il n'y a pas le mois de mars 2013...
Si le jour du mois des dates deb et fin n'a pas d'importance et que seul le mois compte, il faut réinitialiser les dates deb et fin au 1er du mois dans la génération des lignes :

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
SQL> with A as (
select 1 as PK, to_date('06.05.2012','dd.mm.yyyy') as date_debut, to_date('02.03.2013','dd.mm.yyyy') as date_fin from dual union all
select 2      , to_date('02.09.2012','dd.mm.yyyy')              , to_date('06.10.2012','dd.mm.yyyy')             from dual
)
SELECT rownum as PK, t.PK as FK, 
       extract(month FROM add_months(t.date_debut, to_number(x.column_value)-1)) AS "MONTH",
       extract(year  FROM add_months(t.date_debut, to_number(x.column_value)-1)) AS "YEAR"
  FROM A t
 CROSS JOIN TABLE(
              cast(multiset(
                      SELECT level
                        FROM dual
                     connect BY level <= months_between (to_date('01/'||to_char(date_fin,'mm/yyyy'),'dd/mm/yyyy'),to_date('01/'||to_char(date_debut,'mm/yyyy'),'dd/mm/yyyy'))+1
                           ) AS sys.odcivarchar2list 
                   )
                 ) x
/
 
        PK         FK      MONTH       YEAR
---------- ---------- ---------- ----------
         1          1          5       2012
         2          1          6       2012
         3          1          7       2012
         4          1          8       2012
         5          1          9       2012
         6          1         10       2012
         7          1         11       2012
         8          1         12       2012
         9          1          1       2013
        10          1          2       2013
        11          1          3       2013
        12          2          9       2012
        13          2         10       2012
 
13 rows selected.
 
SQL>

Vous pouvez essayer de faire une première requête qui ramène les lignes de 1 à N, avec N le max souhaité, puis faire une jointure sur la table pour "créer" les différents mois, plutôt que de faire tout en récursif directement sur la table. Par contre, c'est uniquement instinctif, il faudrait bien sûr tester ce que ça donne en terme de performance :
Code:

1 2 3 4 5 6 7 WITH maxiA AS (SELECT MAX(TRUNC(months_between (date_fin,date_debut) ) +1) AS maxi FROM A) , nbLignes AS (SELECT level AS n FROM maxiA CONNECT BY level <= maxiA.maxi) SELECT Pk ,extract(month FROM add_months(date_debut, n-1)) AS MONTH ,extract(yeaR FROM add_months(date_debut, n-1)) AS YEAR FROM A INNER JOIN nbLignes ON ( n <= TRUNC(months_between (date_fin,date_debut) ) +1 )

22/11/2012, 11h40
Linda_01

Merci Rei!

Effectivement, via votre technique, c'est quasi instantané! :)
un grand merci :)

On peut aussi faire ainsi :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH A AS
(
SELECT 1 AS PK, date '2012-05-06' AS date_debut, date '2013-03-02' AS date_fin FROM dual union ALL
SELECT 2      , date '2012-09-02'              , date '2012-10-06'             FROM dual
)
    select row_number() over(order by pk asc, extract(year FROM add_months(date_debut, level-1)) asc, extract(month FROM add_months(date_debut, level-1)) asc) as pk
         , pk as fk
         , extract(month FROM add_months(date_debut, level-1)) AS MONTH
         , extract(year  FROM add_months(date_debut, level-1)) AS YEAR
      from A
CONNECT BY level <= TRUNC(months_between (last_day(date_fin), trunc(date_debut, 'mm')) ) +1
       AND PRIOR PK = PK
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  ORDER BY pk asc;

Utile pour faire un connect by level pour chaque ligne d'une table !