Database Reference
In-Depth Information
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp
SELECT dept.deptno
FROM dept, emp
WHERE dept.deptno = emp.deptno(+) AND emp.deptno IS NULL
The purpose of these four SQL statements is the same. The results they return are the same as well. Therefore,
you might expect the query optimizer to provide the same execution plan in all cases. This is, however, not what
happens. In fact, only the second and the fourth use the same execution plan. The others are quite different. Note
these execution plans were generated on version 12.1. Other versions can generate different execution plans:
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN ANTI NA | |
| 2 | INDEX FULL SCAN | DEPT_PK |
| 3 | TABLE ACCESS FULL| EMP |
--------------------------------------
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN ANTI | |
| 2 | INDEX FULL SCAN | DEPT_PK |
| 3 | TABLE ACCESS FULL| EMP |
--------------------------------------
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MINUS | |
| 2 | SORT UNIQUE NOSORT| |
| 3 | INDEX FULL SCAN | DEPT_PK |
| 4 | SORT UNIQUE | |
| 5 | TABLE ACCESS FULL| EMP |
---------------------------------------
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN ANTI | |
| 2 | INDEX FULL SCAN | DEPT_PK |
| 3 | TABLE ACCESS FULL| EMP |
--------------------------------------
