This discussion is archived
4 Replies Latest reply: Nov 12, 2012 7:16 AM by user10274093 RSS

comparing Explan Plan

user10274093 Explorer
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points