SQL> set lines 150;
SQL> explain plan for select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+);Explained.SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2341341676---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 560 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("A"."MGR"="B"."EMPNO"(+))Note
-----
- dynamic sampling used for this statement19 rows selected.===============================================================================================
SQL> explain plan for select e.ename,(select ename from emp where e.mgr=empno) mgr from emp e;Explained.SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 4000517069--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 280 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("EMPNO"=:B1)Note
-----
- dynamic sampling used for this statement19 rows selected.SQL>
SQL> explain plan for select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+);Explained.SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 2341341676---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 560 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 560 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("A"."MGR"="B"."EMPNO"(+))Note
-----
- dynamic sampling used for this statement19 rows selected.===============================================================================================
SQL> explain plan for select e.ename,(select ename from emp where e.mgr=empno) mgr from emp e;Explained.SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 4000517069--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 280 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 20 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 280 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - access("EMPNO"=:B1)Note
-----
- dynamic sampling used for this statement19 rows selected.SQL>
1、一般来说对于大数据量的表来说第一种外连接的执行速度要快一点
2、对于小数据量的表,子查询的数据表的数据都可以读取到内存中
这种情况下,减少了大量的io方面的消耗,子查询的方式会快一些