Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Jointure externe avec "select in"


Sujet :

SQL Oracle

  1. #1
    Futur Membre du Club
    Jointure externe avec "select in"
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    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 "(+)" ?

  2. #2
    Modérateur

    Si tu utilisais la syntaxe normalisée avec outer join, tu te poserais moins de questions
    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
    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
                )
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  3. #3
    Futur Membre du Club
    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 ?

  4. #4
    Modérateur

    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.
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  5. #5
    Futur Membre du Club
    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 :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    select xxx
    from table1 a, table2 b
    where a.id_table2 = b.id


    donc si je pouvais faire :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    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...

  6. #6
    Modérateur

    Tu peux le faire, mais ce sera beaucoup moins joli que :
    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
    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
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  7. #7
    Futur Membre du Club
    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.

  8. #8
    Expert confirmé
    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 )

  9. #9
    Membre émérite
    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)

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  10. #10
    Membre éprouvé
    @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 :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
    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
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  11. #11
    Membre émérite
    Typiquement je pensais à cet Example Mohammed, quand je parlais d'aggréger d'abord :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    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".

    (c'est ma photo)
    Paku, Paku !
    Pour les jeunes incultes : non, je ne suis pas un pokémon...

    Le pacblog : http://pacmann.over-blog.com/

  12. #12
    Membre éprouvé

    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

    Merci pour la précision.
    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
    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
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"