Jointure externe avec "select in"

Version imprimable

Bonjour,

j'utilise SQL Developper sur une base Oracle.
j'ai une table de transformateurs électriques et une table de mouvements sur ces transformateurs
un transformateur peut avoir 0 à n mouvements
Je veux récupérer l'intégralité de mes transfos, et si le transfo possède au moins un mouvement, récupérer le mouvement le plus récent

pour joindre mes 2 tables, j'ai fait au plus simple :
Code:

1 2 3 4 5 6 7 8 9 select transfo.CODE, transfo.ANNEE_FABRICATION, transfo.FABRICANT, mvt.code_mouvement, mvt.date_mouvement, mvt.motif_mouvement, from E_TRANSFORMATEUR transfo, e_mouvement_transformateur mvt where transfo.id = mvt.e_transformateur(+)
ça, ça fonctionne.

mais pour récupérer uniquement le dernier mvt, je voudrais y ajouter cette clause tout en restant sur une jointure externe :
Code:

1 2 3 4 and mvt.date_mouvement in (select max(mvt2.date_mouvement) from e_mouvement_transformateur mvt2 where transfo.id = mvt2.e_transformateur )
Et là je sèche... ou dois-je ajouter le "(+)" ?

Si tu utilisais la syntaxe normalisée avec outer join, tu te poserais moins de questions :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT  transfo.CODE 
    ,   transfo.ANNEE_FABRICATION 
    ,   transfo.FABRICANT 
    ,   mvt.code_mouvement 
    ,   mvt.date_mouvement 
    ,   mvt.motif_mouvement
FROM    E_TRANSFORMATEUR            transfo
    LEFT OUTER JOIN
        e_mouvement_transformateur  mvt
        ON  transfo.id = mvt.e_transformateur
        AND EXISTS
            (   SELECT  NULL
                FROM    e_mouvement_transformateur mvt2
                WHERE   mvt.e_transformateur    = mvt2.e_transformateur
                HAVING  MAX(mvt2.date_mouvement) = mvt.date_mouvement
            )

13/06/2013, 15h48
rami_rezi

Ah oui, j'y avais pensé... ;)

mais ma requête finale étant bcp plus complexe, avec d'autres tables jointes, est-ce que je peux intégrer ce outer join entre les 2 table en question et continuer à joindre les autres normalement ?
13/06/2013, 15h57
al1_24

Quand tu dis normalement, tu veux dire conformément à la norme ? ;)

Tu verras, à l'usage, les requêtes sont beaucoup plus lisibles (donc maintenables) quand on associe les conditions de jointure à la table concernée et qu'on les sépare des conditions de restriction.

comme tu as du le comprendre, je ne suis pas un expert SQL... ;)

quand je dit "normalement" c'est en faisant ce genre de jointure :
Code:

1 2 3 select xxx from table1 a, table2 b where a.id_table2 = b.id
donc si je pouvais faire :
Code:

1 2 3 4 5 select xxx from table 1a, table 2 b, table 3 c left outer join table 4 d on(c.id_table4 = d.id) and exists(blabla), table 5 e,...etc...

Tu peux le faire, mais ce sera beaucoup moins joli que :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  xxx
FROM    TABLE1  a
    INNER JOIN
        TABLE2  b
        ON  b.id  = a.id_table2 
    INNER JOIN
        TABLE3  c 
        ON  c.id  = ?.xxx
    LEFT OUTER JOIN
        TABLE4  d
        ON  d.id  = c.id_table4
        AND EXISTS(blabla)
    INNER JOIN
        TABLE5 e
        ON  e.id = z.xxx
        ...etc...
WHERE   -- Les conditions qui restent

13/06/2013, 17h43
rami_rezi

oui.. je trouve que ça se discute.
pas obligatoirement plus lisible, car des km de lignes sur de grosses requêtes mettant en oeuvre de nombreuses tables...

pour revenir à l'outer join... j'ai réussi à l'intégrer à la requête finale (13 tables), mais ça devient tres peu performant (à savoir que j'ai plus de 1 M de transfos à rapatrier...)
Je n'ai pas réussi à terminer la requête en 1 heure de temps
je ne suis pas sur de pouvoir m'en sortir. Et je n'ai aucun moyen de demander aux DBA d'améliorer les performances de la base de données si ça peut se régler comme cela.
En join "simple", je l'exécutais en qq minutes pour récupérer près de 800 000 transfos, mais bien sur je ne récuperais pas ceux qui n'avaient pas de mouvement.
13/06/2013, 17h53
skuatamad

Pas besoin de passer par un IN, regarde KEEP dense_rank dans sa forme agrégée pour récupérer les données relatives au dernier mouvement.
Donc dans l'exemple, une seule jointure que tu peux laisser en (+), reste à voir si ça s'intègre facilement à la requête complète.

Juste en passant, je ne suis pas du tout fan du mélange des syntaxes, pour moi c'est tout ANSI ou tout (+) (et je préfère tout ANSI ;) )
14/06/2013, 09h36
pacmann

Salut,

Concernant la forme "normalisée" des jointures, à mon sens c'est justement quand les requêtes sont grosses que c'est agréable : tu te retrouves avec pleins de filtres, et c'est agréable de ne pas mélanger dans le where condition de jointures et filtres.

Dans ton cas d'ailleurs sur l'outer join, tu t'aperçois que c'est presque indispensable.

Cela dit, si ton champ date est NOT NULL, tu peux ajouter à ta condition le NULL : ... AND ( tadata is null or tadata = (select max(tadate ...))...

Autre possibilité encore, c'est de faire la jointure externe non pas sur les mouvements, mais sur les mouvements agrégés (ce qui est un peu du même esprit que la version KEEP DENSE_RANK suggérée par Skuat) : en fonction de ton plan d'exécution initial, ça pourrait être plus performant... (enfin pour ça, il faudrait le plan d'exécution de la chose pour voir comment il traite le IN/EXISTS)

@rami_rezi

Il ne faut pas mélanger des ANSI joins avec des Oracle joins. Pour votre cas vous pouvez utiliser la requête indiquée par al1_24 ou bien inspirez vous de l'exemple suivant

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
38
39
 
select 
       d.deptno
      ,d.dname
      ,e.hiredate
from
      dept d, emp e
where 
      d.deptno = e.deptno(+)
AND EXISTS
            ( SELECT  NULL
              FROM    emp e2
              WHERE   e.deptno    = e2.deptno
              HAVING  MAX(e2.hiredate) = e.hiredate
              or e.hiredate is null
              )
  ;
 
Plan hash value: 1524892112
 
------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER              |      |      1 |        |      4 |00:00:00.01 |     271 |       |       |          |
|*  2 |   HASH JOIN OUTER    |      |      1 |     15 |     16 |00:00:00.01 |      31 |  1155K|  1155K|  601K (0)|
|   3 |    TABLE ACCESS FULL | DEPT |      1 |      3 |      4 |00:00:00.01 |      15 |       |       |          |
|   4 |    TABLE ACCESS FULL | EMP  |      1 |     15 |     15 |00:00:00.01 |      16 |       |       |          |
|*  5 |   FILTER             |      |     16 |        |      4 |00:00:00.01 |     240 |       |       |          |
|   6 |    SORT AGGREGATE    |      |     16 |      1 |     16 |00:00:00.01 |     240 |       |       |          |
|*  7 |     TABLE ACCESS FULL| EMP  |     16 |      5 |     81 |00:00:00.01 |     240 |       |       |          |
------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NOT NULL)
   2 - access("D"."DEPTNO"="E"."DEPTNO")
   5 - filter((MAX("E2"."HIREDATE")=:B1 OR :B2 IS NULL))
   7 - filter("E2"."DEPTNO"=:B1)

Si vous avez des problèmes de performance, commencez par poster ici le plan d'exécution correspondant comme je l'ai fait plus haut

Typiquement je pensais à cet Example Mohammed, quand je parlais d'aggréger d'abord :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT d.deptno ,d.dname ,e.hiredate FROM dept d, (select deptno, max(hiredate) hiredate from emp group by deptno) e WHERE d.deptno = e.deptno(+) ;
Il y a de fortes chances que ça tourne mieux ainsi...

[EDIT] et à préciser que hiredate doit être NOT NULL pour la méthode sous-requête EXISTS. Sinon, il faut remplacer "e.hiredate is null" par "e.empno is null".

Citation:

et à préciser que hiredate doit être NOT NULL pour la méthode sous-requête EXISTS. Sinon, il faut remplacer "e.hiredate is null" par "e.empno is null"

Bien vu :ccool:

Merci pour la précision.

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
38
39
40
41
42
43
44
45
 
update emp set hiredate = null where empno = 7369;
SELECT 
       d.deptno
      ,d.dname
      ,e.hiredate
FROM
      dept d, emp e
WHERE 
      d.deptno = e.deptno(+)
AND EXISTS
            ( SELECT  NULL
              FROM    emp e2
              WHERE   e.deptno    = e2.deptno
              HAVING  MAX(e2.hiredate) = e.hiredate
              OR e.hiredate IS NULL
              )
  ;
20	RESEARCH	
30	SALES	        03/12/1981 00:00:00
10	ACCOUNTING	23/01/1982 00:00:00
20	RESEARCH	08/08/2012 15:05:19
40	ORACLE	
 
SELECT 
       d.deptno
      ,d.dname
      ,e.hiredate
FROM
      dept d, emp e
WHERE 
      d.deptno = e.deptno(+)
AND EXISTS
            ( SELECT  NULL
              FROM    emp e2
              WHERE   e.deptno    = e2.deptno
              HAVING  MAX(e2.hiredate) = e.hiredate
              OR e.empno IS NULL
              )
  ;
 
30	SALES	        03/12/1981 00:00:00
10	ACCOUNTING	23/01/1982 00:00:00
20	RESEARCH	08/08/2012 15:05:19
40	ORACLE