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")