explain-join-methods

来源：互联网发布：淘宝网女式运动鞋编辑：程序博客网时间：2024/06/07 23:21

--理解merge join

SQL> explain plan for  2   select * from emp,dept where dept.deptno=emp.deptno;Explained.SQL>  select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 844388907----------------------------------------------------------------------------------------| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |         |    15 |   855 |     6  (17)| 00:00:01 ||   1 |  MERGE JOIN                  |         |    15 |   855 |     6  (17)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 ||   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 ||*  4 |   SORT JOIN                  |         |    15 |   555 |     4  (25)| 00:00:01 ||   5 |    TABLE ACCESS FULL         | EMP     |    15 |   555 |     3   (0)| 00:00:01 |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------

从执行计划看。

首先对单表做排序(sort emp), dept表访问路径为index，不在需要排序，排序完成之后。做行的合并。

----理解 NESTED LOOPS

SQL> explain plan for  2  select * from emp,dept where dept.deptno!=emp.deptno;Explained.SQL>  select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 4192419542---------------------------------------------------------------------------| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |      |    45 |  2565 |    11   (0)| 00:00:01 ||   1 |  NESTED LOOPS      |      |    45 |  2565 |    11   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 ||*  3 |   TABLE ACCESS FULL| EMP  |    11 |   407 |     2   (0)| 00:00:01 |---------------------------------------------------------------------------

首先dept表为驱动表。取出一条数据，去遍历emp表，然后取出dept的第2条数据，去遍历emp，。。。一直到dept的结果取完
--在这个过程中，不断给结果集输出到窗口，直到DEPT表取出所有行。执行完毕.

--理解NESTED LOOPS SEMI

SQL> explain plan for  2  select * from emp where exists(select 1 from dept where dept.deptno=emp.deptno);Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 3274513678------------------------------------------------------------------------------| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |         |    15 |   600 |     3   (0)| 00:00:01 ||   1 |  NESTED LOOPS SEMI |         |    15 |   600 |     3   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| EMP     |    15 |   555 |     3   (0)| 00:00:01 ||*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    12 |     0   (0)| 00:00:01 |------------------------------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

--首先驱动表为emp。去扫描dept表的唯一index，emp表取出第一行，去匹配 dept index 的键值，找到匹配的记录直接跳出嵌套循环。
--然后取emp表的第二行，去匹配dept index 的键值，找到匹配的记录直接跳出嵌套循环。
--在这个过程中，不断给结果集输出到窗口，直到emp表取出所有行。执行完毕

----理解 NESTED LOOPS ANTI

SQL> explain plan for  2  select * from emp where not exists(select 1 from dept where dept.deptno=emp.deptno);Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 3496123964------------------------------------------------------------------------------| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |         |     1 |    40 |     3   (0)| 00:00:01 ||   1 |  NESTED LOOPS ANTI |         |     1 |    40 |     3   (0)| 00:00:01 ||   2 |   TABLE ACCESS FULL| EMP     |    15 |   555 |     3   (0)| 00:00:01 ||*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    12 |     0   (0)| 00:00:01 |------------------------------------------------------------------------------

--首先驱动表为emp。emp 取第一条数据，去匹配dept表的唯一index键值，如果不匹配，那么一直要遍历index 的键值所有索引条目，直到index遍历完，
--emp的第一条匹配完成，每次不匹配的记录返回给窗口。
--然后emp 取第2条记录，如果在匹配dept的键值的时候，发现匹配了，这里是相等，那直接跳出扫描，第2条记录不返回窗口
--然后emp继取续第三条。。。匹配了不返回，不匹配返回给窗口。
--直到emp表取出所有行。执行完毕