Sunday, February 7, 2010

Subquery Performance Tuning (Part 1, IN, EXISTS and COUNT >0)


SQL> select * from employees e
2 where (select count(*) from employees f where e.employee_id=f.manager_id) > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 2241443404

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 4 | | |
|* 4 | INDEX RANGE SCAN| EMP_MANAGER_IX | 6 | 24 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

1 - filter( (SELECT COUNT(*) FROM "EMPLOYEES" "F" WHERE
"F"."MANAGER_ID"=:B1)>0)
4 - access("F"."MANAGER_ID"=:B1)



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

Execution Plan
----------------------------------------------------------
Plan hash value: 259920726

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 1296 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 18 | 1296 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_MANAGER_IX | 18 | 72 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

3 - access("F"."MANAGER_ID"="E"."EMPLOYEE_ID")



SQL> select * from employees e
2 where e.employee_id in (select manager_id from employees f);

Execution Plan
----------------------------------------------------------
Plan hash value: 259920726

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 1296 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 18 | 1296 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_MANAGER_IX | 18 | 72 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

3 - access("E"."EMPLOYEE_ID"="MANAGER_ID")