表连接的执行计划

最新推荐文章于 2022-04-21 14:25:58 发布

olnathen

最新推荐文章于 2022-04-21 14:25:58 发布

阅读量528

点赞数

分类专栏： oracle 文章标签：表的连接方式

本文链接：https://blog.csdn.net/olnathen/article/details/12943945

版权

oracle 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

Oracle表、视图、嵌套视图所查询的中间集合等主要通过表连接进行连接的，这其中包括子查询、半连接等。需注意的是表连接指的是两个表之间连接，表可以是视图、子查询等。

Oracle的表连接方式有嵌套循环连接（NESTED LOOPS JOIN）、排序合并连接（SORT MERGE JOIN）、哈希连接（HASH JOIN）、半连接（SEMI JOIN）、笛卡尔连接（CARTESAIN JOIN）、外连接（OUTER JOIN）、索引连接（索引连接）。

1、嵌套循环连接（NESTED LOOPS JOIN）

嵌套循环连接：Oracle先选择一个集合（驱动表/外部表），然后根据集合中的一行数据跟另外一个集合（被驱动表/内部表）中所有的数据进行比较，如符合条件则放入结果集中，然后取驱动表结果集的下一条数据继续进行循环，直到结束。

执行计划如下：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

SQL_ID 3g0a1r2v3tdq5, child number 0
-------------------------------------
select /*+ use_nl(e d) */ e.ename,e.job,d.dname from emp e,dept d
where e.deptno=d.deptno and e.sal<2000

Plan hash value: 3625962092

--------------------------------------------------------------------------------------------------
| Id | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |      1 |         |      8 |00:00:00.01 |      20 |
|   1 | NESTED LOOPS                     |                          |      1 |         |      8 |00:00:00.01 |      20 |
|   2 |   NESTED LOOPS                    |                            |      1 |      4 |      8 |00:00:00.01 |      12 |
|* 3 |    TABLE ACCESS FULL         | EMP                  |      1 |      4 |      8 |00:00:00.01 |       8 |
|* 4 |    INDEX UNIQUE SCAN         | PK_DEPT        |      8 |      1 |      8 |00:00:00.01 |       4 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      8 |      1 |      8 |00:00:00.01 |       8 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("E"."SAL"<2000)
4 - access("E"."DEPTNO"="D"."DEPTNO")

已选择24行。

已用时间: 00: 00: 00.07

嵌套循环注意地方：1、被驱动表join列必须存在索引

2、驱动表返回数据量少

3、嵌套循环不占用pga

2、排序合并连接（SORT MERGE JOIN）

排序合并连接：Oracle会先将两个读取两个源表的数据，然后根据join列进行排序，然后再进行连接。排序合并连接可以处理非等值的连接，这是嵌套循环连接不能满足的，同时排序合并连接解决了嵌套循环随机读取的问题，它最大的特点是在扫描的同时在执行连接操作。但它最大的缺点就是排序，消耗资源，占用pga，当数据源是大表的情况下，执行效率是非常低的。

执行计划如下：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------

SQL_ID 4yyd3c35b6vr7, child number 0
-------------------------------------
select e.ename,e.job,d.dname from emp e,dept d where
e.deptno=d.deptno and e.sal<2000

Plan hash value: 844388907

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                     |      1 |        |      8 |00:00:00.01 |      11 |    | |          |
|   1 | MERGE JOIN                                         |                    |      1 |      4 |      8 |00:00:00.01 |      11 |    | |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT        |      1 |      4 |      4 |00:00:00.01 |       4 |    | |          |
|   3 |    INDEX FULL SCAN                            | PK_DEPT |      1 |      4 |      4 |00:00:00.01 |       2 |    | |          |
|* 4 |   SORT JOIN                                          |                     |      4 |      4 |      8 |00:00:00.01 |       7 | 2048 | 2048 | 2048 (0)|
|* 5 |    TABLE ACCESS FULL                     | EMP          |      1 |      4 |      8 |00:00:00.01 |       7 |    | |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPTNO"="D"."DEPTNO")
       filter("E"."DEPTNO"="D"."DEPTNO")
   5 - filter("E"."SAL"<2000)

已选择25行。

已用时间: 00: 00: 00.21

嵌排序合并连接注意地方：1、排序合并连接是按照扫描的方式对两边已经排序的集合进行连接的，所以join列上有索引并不一定会被使用

2、排序合并连接不适合海量数据使用

3、排序合并连接占用pga，消耗资源，一般情况不考虑走此连接

4、排序合并连接在buffer连接的时候类似于嵌套循环，但不存在多次访问被驱动表的情况

参考内容：

排序首先在内存中进行，能在内存中完成的叫做Optimal Sort，也叫In-Memory Sort。如果需要借助磁盘缓冲，叫做外部排序External Sort。

在外部排序中，运行run是指一次对磁盘做IO。

如果一次输入就能完成整个数据集的排序叫做1路排序1-Pass Sort。需要多次输入输出操作的叫多路排序Multi-Pass Sort。

从性能角度来看Optimal Sort>1-Pass Sort>Multi-Pass Sort

执行计划中

OMem：代表使用Optimal排序需要的内存估量。

1Mem：代表使用1-Pass排序需要的内存估量。

O/1/M：代表实际Optimal、1-Pass、Multi-Pass方式的执行次数。

3、哈希连接（HASH JOIN）

哈希连接是指通过使用哈希函数的方法来实现表连接的方式。所谓的函数，是指为其提供一个变量值在通过函数运算返回一个值的过程，哈希连接只有在使用了“=”比较运算符的情况下才有效

执行计划如下：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID 7xgq78m3mmw7k, child number 0
-------------------------------------
select/*+ full(d) */ e.ename,e.job,d.dname,d.deptno from emp e,dept d
where e.deptno=d.deptno

Plan hash value: 615168685

----------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |      1 |        |     14 |00:00:00.01 |      15 |       |       |          |
|* 1 | HASH JOIN                    |             |      1 |     14 |     14 |00:00:00.01 |      15 |   888K|   888K| 709K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP |      1 |     14 |     14 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("E"."DEPTNO"="D"."DEPTNO")

已选择21行。

已用时间: 00: 00: 00.06

哈希连接（HASH JOIN）需注意的地方：

                    1.HASH JOIN 在OLTP环境一般没什么优化的地方，在OLAP环境中可以利用并行优化HASH JOIN。
                    2.利用等待事件监控HASH JOIN的时候，如果发现在做on-disk HASH JOIN(direct path read/write temp)，
                      可以加大PGA,或者手工设置 work area 分配较大的PGA内存。
                    3.在做SQL优化的时候，你要去检查HASH JOIN的JOIN列(通过HASH JOIN 前面的ID去找ACCESS)选择性，
                      如果HASH JOIN 连接列选择性很低，那么HASH JOIN可能跑很久，这个时候可以自己尝试构造伪列进
                      行JOIN，如果无法构造伪列，这个时候看看能否从业务上优化，就不要想着用SQL优化了。
                    4.HASH JOIN 选择小表做驱动表，小表指的不是表的行数，而是指的是行数*列宽度
                      例子中，选择dept作为驱动表是因为
                      dept大小 4*(dname+loc+deptno)宽度 < emp大小14*(ename,job,sal,deptno)宽度
                      在做HASH JOIN优化的时候要特别注意这点。
                    5.HASH JOIN只能用于等值连接。

4、半连接（SEMI JOIN）

半连接是指两个结果集做连接，但只返回主查询结果集中的数据。在这里将反连接（anti join）列入半连接中，常发生在in 、not in、exists、 not exists等情况中，由于oracle10g后，oracle优化器采用CBO基于成本优化原则，CBO会对sql进行等价转换，而in,not in,exists,not exists是很容易发生sql转换的，所以在使用子查询时要慎重，否则可能会影响性能问题。

执行计划如下：

SQL> select department_name
2   from hr.departments dept
3   where NOT EXISTS (select null from hr.employees emp
4   where emp.department_id = dept.department_id);

已选择16行。

执行计划
----------------------------------------------------------
Plan hash value: 3082375452

----------------------------------------------------------------------------------------
| Id | Operation          | Name              | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |    17 |   323 |     3   (0)| 00:00:01 |
|   1 | NESTED LOOPS ANTI |                   |    17 |   323 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS       |    27 |   432 |     3   (0)| 00:00:01 |
|* 3 |   INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    41 |   123 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")

5、笛卡尔连接（CARTESAIN JOIN）

笛卡尔连接是指两表连接没有任何连接条件，“没有任何连接条件”并不是指不能存在查询条件，而是尽管存在查询条件也不影响两表的结果集连接-M*M的连接。

执行计划如下

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------

SQL_ID 246u0ydjg0bwq, child number 0
-------------------------------------
select * from emp,dept

Plan hash value: 2034389985

------------------------------------------------------------------------------------------------------------------
| Id | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |     56 |00:00:00.01 |      17 |       |       |          |
|   1 | MERGE JOIN CARTESIAN|           |      1 |     56 |     56 |00:00:00.01 |      17 |       |       |          |
|   2 |   TABLE ACCESS FULL    | DEPT |      1 |      4 |      4 |00:00:00.01 |      10 |       |       |          |
|   3 |   BUFFER SORT                |             |      4 |     14 |     56 |00:00:00.01 |       7 | 2048 | 2048 | 2048 (0)|
|   4 |    TABLE ACCESS FULL    | EMP |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
------------------------------------------------------------------------------------------------------------------

已选择16行。

笛卡尔连接需要注意的是只有当两表中其中一表数据返回很少的情况下才使用，否则严重影响性能。

6、外连接（OUTER JOIN）

外连接指的是将要连接的两表，选择其中一表为驱动表，即使连接的另外一个对象不存在记录时，也同样返回数据行的连接。外连接的连接方式有left outer join,right outer join,full outer join，外连接是最常用的一种连接方式，比如嵌套循环外连接，哈希外连接等

执行计划如下：

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------

SQL_ID 80g986cb14zn4, child number 0
-------------------------------------
select a.*,b.* from emp a left join dept b on a.deptno = b.deptno

Plan hash value: 3387915970

----------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      15 |       |       |          |
|* 1 | HASH JOIN OUTER   |      |      1 |     14 |     14 |00:00:00.01 |      15 |   762K|   762K| 594K (0)|
|   2 |   TABLE ACCESS FULL| EMP |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       8 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."DEPTNO"="B"."DEPTNO")

已选择20行。

已用时间: 00: 00: 00.06

7、索引连接

olnathen

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
表连接的执行计划

Oracle表、视图、嵌套视图所查询的中间集合等主要通过表连接进行连接的，这其中包括子查询、半连接等。需注意的是表连接指的是两个表之间连接，表可以是视图、子查询等。 Oracle的表连接方式有嵌套循环连接（NESTED LOOPS JOIN）、排序合并连接（SORT MERGE JOIN）、哈希连接（HASH JOIN）、半连接（SEMI JOIN）、笛卡尔连接（CARTESAIN JOI
复制链接

扫一扫