4 Replies Latest reply: Nov 12, 2012 9:16 AM by user10274093 RSS

    comparing Explan Plan

    user10274093
      Hi,
      can you please tell me which one is better and why :
      SQL> SELECT /* EXISTS example */
        2           e.ename, e.empno
        3    FROM emp e
        4   WHERE EXISTS (SELECT 1 FROM dept o
        5                    WHERE e.empno = o.deptno
        6                      AND o.deptno = 144);
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2272506472
      
      ----------------------------------------------------------------------------------------
      | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |         |     1 |    13 |     1   (0)| 00:00:01 |
      |   1 |  NESTED LOOPS                |         |     1 |    13 |     1   (0)| 00:00:01 |
      |   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    10 |     1   (0)| 00:00:01 |
      |*  3 |    INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 00:00:01 |
      |*  4 |   INDEX UNIQUE SCAN          | PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("E"."EMPNO"=144)
         4 - access("O"."DEPTNO"=144)
             filter("E"."EMPNO"="O"."DEPTNO")
      SQL> SELECT /* IN example */
        2           e.ename, e.empno
        3    FROM emp e
        4   WHERE empno in (SELECT 1 FROM dept o
        5                    WHERE e.empno = o.deptno
        6                      AND o.deptno = 144);
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 860118780
      
      -----------------------------------------------------------------------------------------
      | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |         |     1 |    13 |     0   (0)|          |
      |*  1 |  FILTER                       |         |       |       |            |          |
      |   2 |   NESTED LOOPS                |         |     1 |    13 |     1   (0)| 00:00:01 |
      |   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |    10 |     1   (0)| 00:00:01 |
      |*  4 |     INDEX UNIQUE SCAN         | PK_EMP  |     1 |       |     0   (0)| 00:00:01 |
      |*  5 |    INDEX UNIQUE SCAN          | PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
      -----------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(NULL IS NOT NULL)
         4 - access("EMPNO"=1)
         5 - access("O"."DEPTNO"=1)
      Thanks.
        • 1. Re: comparing Explan Plan
          Ulfet Tanriverdiyev
          Tom was explained about this question.
          Please refer to : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:442029737684
          • 2. Re: comparing Explan Plan
            Mohamed Houri
            Dear,

            Have you tested your two queries? I have slighly changed your two queries and executed them. Have a look
            SELECT /* EXISTS example */
                         e.ename, e.empno
                  FROM emp e
                 WHERE EXISTS (SELECT 1 FROM dept o
                                  WHERE e.deptno = o.deptno
                                    AND o.deptno = 20);
            
            ENAME           EMPNO
            ---------- ----------
            smith            7369
            jones            7566
            scott            7788
            adams            7876
            ford             7902
            and
            SELECT /* IN example */
                         e.ename, e.empno
                  FROM emp e
                 WHERE empno in (SELECT 1 FROM dept o
                                  WHERE e.deptno = o.deptno
                                    AND o.deptno = 20);
            no rows selected
            There is no employee (in departement 20) having en empno = 1. But it exists 5 employees belonging to departement 20

            Regards
            • 3. Re: comparing Explan Plan
              riedelme
              Mohomed pointed out the error in the second query, but I don't think that's what you meant - did you mean which is better, IN or EXISTS?

              I don't understand why you were joining the emplyee ID to the department ID. Did you mean something like
              SQL> SELECT /* IN example */
                2           e.ename, e.empno
                3    FROM emp e
                4   WHERE deptno in (SELECT deptno FROM dept o and deptno = 144)
                5  
              In the case of your example its hard to tell. The execution plans are identical with identical statistics. The predicate lists were different.

              I personally prefer correlated EXISTS subqueries to IN lists, although if coded correctly either should work. EXISTS subqueries can stop when the value is found but IN searches the entire list even after a value is found
              • 4. Re: comparing Explan Plan
                user10274093
                Thanks.