Sunday, February 7, 2010

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