1 2 Previous Next 23 Replies Latest reply: Sep 20, 2013 2:49 AM by MarkCooper Go to original post RSS
      • 15. Re: Difference-  in conditions (Join and Where Clause)
        Jonathan Lewis

        SolomonYakobson wrote:

         

        Why do you think it is a bug? I'd say it is correct behavior. According to hierarchical query rules (as Frank already noted) WHERE clause is applied afterwards. So by expanding in-line view we would clearly violate it.

         

        SY.

         

        Saloman,

         

        Do you happen to have a convenient link that you can point me to that gives that as the specification for the behaviour. I had interpreted Frank's comment as an observation of what does happens, rather than a demonstration of what the specification dictates.

         

         

        Regards

        Jonathan Lewis

        • 16. Re: Difference-  in conditions (Join and Where Clause)
          Jonathan Lewis

          If anyone's interested, here are the "unparsed" versions of the queries - taken from a 12c 10053 trace file:

           

          SELECT
          E.ENAME ENAME,E.JOB JOB,D.DNAME DNAME,LEVEL LVL
          FROM
          EMP E,
          DEPT D
          WHERE
          E.JOB<>'PRESIDENT'
          START WITH
          E.MGR IS NULL AND E.DEPTNO=D.DEPTNO
          CONNECT BY
          E.MGR=PRIOR E.EMPNO AND E.DEPTNO=D.DEPTNO


          SELECT E.ENAME ENAME,E.JOB JOB,D.DNAME DNAME,LEVEL LVL
          FROM
          EMP E,
          DEPT D
          START WITH
          E.MGR IS NULL AND E.DEPTNO=D.DEPTNO AND E.JOB<>'PRESIDENT'
          CONNECT BY
          E.MGR=PRIOR E.EMPNO AND E.DEPTNO=D.DEPTNO AND E.JOB<>'PRESIDENT'

           

           

           

          Regards

          Jonathan Lewis


          • 17. Re: Difference-  in conditions (Join and Where Clause)
            BrendanP

            SQL Manual, v11.2, p.9-4:

             

            If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

             

            In fact, this is a useful feature that makes the functionality more general - you can still code the query to eliminate the children if you want to.

            • 18. Re: Difference-  in conditions (Join and Where Clause)
              Jonathan Lewis

              BrendanP wrote:

               

              SQL Manual, v11.2, p.9-4:

               

              If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

               

              In fact, this is a useful feature that makes the functionality more general - you can still code the query to eliminate the children if you want to.

              BrendanP,

               

              Thanks for the reference - in fact the bit just above your quote (in the 12c manual) is even more explicit:

               

               

              Oracle processes hierarchical queries as follows:

              ■ A join, if present, is evaluated first, whether the join is specified in the FROM clause

              or with WHERE clause predicates.

              ■ The CONNECT BY condition is evaluated.

              ■ Any remaining WHERE clause predicates are evaluated.

               

               

              Regards

              Jonathan Lewis

              • 19. Re: Difference-  in conditions (Join and Where Clause)
                Frank Kulash

                Hi, Jonathan,

                 

                 

                JonathanLewis wrote:

                 

                ... Do you happen to have a convenient link that you can point me to that gives that as the specification for the behaviour. I had interpreted Frank's comment as an observation of what does happens, rather than a demonstration of what the specification dictates. ...

                 

                 

                Depending on what you mean by "waht the specification dictates", it's both.

                The SQL Language manual

                http://docs.oracle.com/cd/E11882_01/server.112/e26088/queries003.htm#sthref2227

                says:

                 

                Oracle processes hierarchical queries as follows:

                •   A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates. 
                • The CONNECT BY condition is evaluated.

                • Any remaining WHERE clause predicates are evaluated.

                • 20. Re: Difference-  in conditions (Join and Where Clause)
                  Solomon Yakobson

                  Jonathan,

                   

                  This is what I am getting in 12C using:

                   

                  set serveroutput on

                  declare

                      v_c clob;

                  begin

                      dbms_utility.expand_sql_text(

                                                   'SELECT  ename, job

                                                   ,       dname

                                                   ,       LEVEL   AS lvl

                                                   FROM (

                                                   select e.*, d.*

                                                   from

                                                     scott.emp   e

                                                   JOIN    scott.dept  d   ON    e.deptno = d.deptno

                                                   --      AND     job     != ''PRESIDENT''

                                                   WHERE   job     != ''PRESIDENT''

                                                   )

                                                   START WITH  mgr     IS NULL

                                                   CONNECT BY  mgr     = PRIOR empno',

                                                   v_c

                                                  );

                      dbms_output.put_line(v_c);

                  end;

                  /

                   

                  I formatted output to more readable:

                   

                  SELECT  "A6"."ENAME" "ENAME",

                          "A6"."JOB" "JOB",

                          "A6"."DNAME" "DNAME",

                          LEVEL "LVL"

                    FROM  (

                           SELECT  "A7"."EMPNO_0" "EMPNO",

                                   "A7"."ENAME_1" "ENAME",

                                   "A7"."JOB_2" "JOB",

                                   "A7"."MGR_3" "MGR",

                                   "A7"."HIREDATE_4" "HIREDATE",

                                   "A7"."SAL_5" "SAL",

                                   "A7"."COMM_6" "COMM",

                                   "A7"."QCSJ_C000000001700000_7" "DEPTNO",

                                   "A7"."QCSJ_C000000001700001_8" "DEPTNO",

                                   "A7"."DNAME_9" "DNAME",

                                   "A7"."LOC_10" "LOC"

                             FROM  (

                                    SELECT  "A9"."EMPNO" "EMPNO_0",

                                            "A9"."ENAME" "ENAME_1",

                                            "A9"."JOB" "JOB_2",

                                            "A9"."MGR" "MGR_3",

                                            "A9"."HIREDATE" "HIREDATE_4",

                                            "A9"."SAL" "SAL_5",

                                            "A9"."COMM" "COMM_6",

                                            "A9"."DEPTNO" "QCSJ_C000000001700000_7",

                                            "A8"."DEPTNO" "QCSJ_C000000001700001_8",

                                            "A8"."DNAME" "DNAME_9",

                                            "A8"."LOC" "LOC_10"

                                      FROM  "SCOTT"."EMP" "A9",

                                            "SCOTT"."DEPT" "A8"

                                      WHERE "A9"."DEPTNO" = "A8"."DEPTNO"

                                   ) "A7"

                             WHERE "A7"."JOB_2"<>'PRESIDENT'

                          ) "A6"

                    START WITH "A6"."MGR" IS NULL

                    CONNECT BY "A6"."MGR"=PRIOR "A6"."EMPNO"

                   

                  SY.

                  • 21. Re: Difference-  in conditions (Join and Where Clause)
                    MarkCooper

                    Hi Guys,

                    To reply to all - as I understood.

                    1. its good practise to use conditions/ Filter (apart from ON clause)  in WHERE Clause instead Inner join ? right ?

                    2.  Now , in my previous eg. we could use Location id in Where clause as it was in both tables.

                         What is the better practise to use Location id here ( 1 /2) ?

                     

                    1. Here I am using location filter in Inner join condition ( suppose Location id is not in item table))-

                    "SELECT I.*, Gl * From Sc1.Item I

                       Inner Join Sc1.Part P

                                On P.Part_Id = I.Part_Id

                           Inner Join Sc1.Location Gl

                                On Gl.Location_Id = I.Location_Id

                                  And Gl.Location_Id In ( 1767, 1747,202,1625)

                        Inner Join Sc1.Condition C

                            On C.Condtion_Id = Gl.Condition_Id

                    Where  I.Inactive_Ind = 0

                          And I.Condition_Id != 325

                            

                    2. Here I am using location filter in Where clause  ( suppose Location id is not in item table)-        

                              SELECT I.*, Gl * From Sc1.Item I

                       Inner Join Sc1.Part P

                                On P.Part_Id = I.Part_Id

                           Inner Join Sc1.Location Gl

                                On Gl.Location_Id = I.Location_Id

                            Inner Join Sc1.Condition C

                            On C.Condtion_Id = Gl.Condition_Id

                    Where  I.Inactive_Ind = 0

                            And Gl.Location_Id In ( 1767, 1747,202,1625)

                              And I.Condition_Id != 325

                     

                    Thanks,

                    • 22. Re: Difference-  in conditions (Join and Where Clause)
                      Frank Kulash

                      Hi,

                       

                      MarkCooper wrote:

                       

                      Hi Guys,

                      To reply to all - as I understood.

                      1. its good practise to use conditions/ Filter (apart from ON clause)  in WHERE Clause instead Inner join ? right ?

                      2.  Now , in my previous eg. we could use Location id in Where clause as it was in both tables.

                           What is the better practise to use Location id here ( 1 /2) ?

                       

                      1. Here I am using location filter in Inner join condition ( suppose Location id is not in item table))-

                      "SELECT I.*, Gl * From Sc1.Item I

                         Inner Join Sc1.Part P

                                  On P.Part_Id = I.Part_Id

                             Inner Join Sc1.Location Gl

                                  On Gl.Location_Id = I.Location_Id

                                    And Gl.Location_Id In ( 1767, 1747,202,1625)

                          Inner Join Sc1.Condition C

                              On C.Condtion_Id = Gl.Condition_Id

                      Where  I.Inactive_Ind = 0

                            And I.Condition_Id != 325

                            

                      2. Here I am using location filter in Where clause  ( suppose Location id is not in item table)-        

                                SELECT I.*, Gl * From Sc1.Item I

                         Inner Join Sc1.Part P

                                  On P.Part_Id = I.Part_Id

                             Inner Join Sc1.Location Gl

                                  On Gl.Location_Id = I.Location_Id

                              Inner Join Sc1.Condition C

                              On C.Condtion_Id = Gl.Condition_Id

                      Where  I.Inactive_Ind = 0

                              And Gl.Location_Id In ( 1767, 1747,202,1625)

                                And I.Condition_Id != 325

                       

                      Thanks,

                      If location_id is not in the item table, then the join condition

                       

                      On Gl.Location_Id = I.Location_Id

                      will cause an error.

                       

                      Once again, it should not affect either results or performance if a condition like

                       

                      Gl.Location_Id In ( 1767, 1747,202,1625)

                      is in the ON clause or in the WHERE clause.  It doesn't matter whether or not there happens to be a column called location_id in any other table, or if the same column Gl.Location_Id is used in other conditions.

                      Since that condition only references one table (GI), I recommend putting it in a WHERE clause, just to make the code clearer.

                       

                      Once again, this only applies to inner joins, not to outer joins, and not to CONNECT BY queries.

                      1 2 Previous Next