explain-join-methods_explain join-优快云博客

本文链接：https://blog.youkuaiyun.com/kelly1984/article/details/8692777

--理解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表取出所有行。执行完毕