--脚本
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a(ID NUMBER,NAME VARCHAR2(10));
CREATE TABLE b(ID NUMBER,NAME VARCHAR2(10));
INSERT INTO a VALUES(1,'a');
INSERT INTO a VALUES(2,'b');
INSERT INTO a VALUES(3,'c');
INSERT INTO b VALUES(1,'a');
INSERT INTO b VALUES(2,'b');
INSERT INTO b VALUES(4,'d');
COMMIT;
SQL> select * from a;
ID NAME
---------- ----------
1 a
2 b
3 c
SQL> select * from b;
ID NAME
---------- ----------
1 a
2 b
4 d
SQL> select a.id,a.name,b.id,b.name from a full join b on a.id=b.id;
ID NAME ID NAME
---------- ---------- ---------- ----------
1 a 1 a
2 b 2 b
3 c
4 d
执行计划
----------------------------------------------------------
Plan hash value: 2192011130
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 160 | 13 (8)| 00:00:01 |
| 1 | VIEW | | 4 | 160 | 13 (8)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 30 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 15 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 15 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN ANTI | | 1 | 8 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 15 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 9 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."ID"="B"."ID"(+))
6 - access("A"."ID"="B"."ID")
执行计划的大致顺序是:由内到外,由上到下
执行顺序:
4:全表扫描A表(内表),获取3条记录
5:全表扫描B表(外表),获取3条记录
3:两表做HASH JOIN OUTER,A表为内表,B表为外表。获取3条记录;步骤3-5的执行计划与A LEFT JOIN B计划相似。可以理解为以"A"."ID"="B"."ID"为连接条件,做LEFT JOIN连接,得到结果如下
ID NAME ID NAME
---------- ---------- ---------- ----------
1 a 1 a
2 b 2 b
3 c
7:全表扫描B表,获取3条记录
8:全表扫描A表,获取3条记录
6:两表做HASH JOIN ANTI,B表为内表,A表为外表。获取1条记录
该记录为: null null 4 d --推测HASH JOIN ANTI算法的原理是找出在B表中出现而没有在A表中出现的记录?
2:针对步骤3和步骤6得到的结果,做UNION ALL运算,得到如下结果
ID NAME ID NAME
---------- ---------- ---------- ----------
1 a 1 a
2 b 2 b
3 c
4 d
1:把步骤2得到的结果封装到一个中间视图中?
0:通过SELECT对步骤1视图查询,得到最终结果。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25806858/viewspace-742240/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25806858/viewspace-742240/