Discussion :

[Linda_01]

Bonsoir,

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

Voici la table source A:

Code :

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

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 :

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

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 :

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

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 ?

[Waldar]

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

[skuatamad]

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

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

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

[Rei Ichido]

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 :

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

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 )

[Linda_01]

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

[Waldar]

On peut aussi faire ainsi :

Code :

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

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 !