Subquery Performance Tuning (Part 2, NOT IN and NOT EXISTS))

SQL> select * from employees e
  2  where e.department_id not in (select department_id from employees);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2584987836

-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |   146 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT ANTI NA|           |     1 |   146 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL     | EMPLOYEES |   107 |  1391 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL     | EMPLOYEES |   107 | 14231 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="DEPARTMENT_ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        881  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select * from employees e
  2  where not exists (select 1 from employees f where e.department_id=f.department_id);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER      HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- --------

JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
        178 Kimberely            Grant                     KGRANT                    011.44.1644.429263   24-MAY-9

SA_REP           7000            .15        149



Execution Plan
----------------------------------------------------------
Plan hash value: 2796935524

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     1 |   146 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT ANTI|           |     1 |   146 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  1391 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | EMPLOYEES |   107 | 14231 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="F"."DEPARTMENT_ID")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       1084  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from employees e
  2  where not exists (select 1 from employees f where e.department_id=f.department_id)
  3  and e.department_id is not null;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2796935524

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     1 |   146 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT ANTI|           |     1 |   146 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMPLOYEES |   107 |  1391 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL  | EMPLOYEES |   106 | 14098 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="F"."DEPARTMENT_ID")
   3 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        881  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed