Oracle-SQL性能优化（4）-连接方式

原创大柏树 2022-10-31

1514

表（结果集）与表（结果集）之间的连接方式非常重要，选择合适的连接方式很关键，如果选择错误的连接方式，执行效率可能就相差甚远了。
在多表关联的时候，一般情况下只能是两个表先关联，两表关联之后的结果再和其他结果集关联。先熟悉最常见的几种连接方式。

1.嵌套循环 NESTED LOOPS

算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就被扫描多少次。
示例：关联scott.emp和scott.dept表，emp作为驱动表。

SQL> select /*+ gather_plan_statistics use_nl(e,d) leading(e) */ e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	9ddpcbaychbwr, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(e,d) leading(e) */
e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno
Plan hash value: 3625962092
--------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |      1 |	 |     14 |00:00:00.01 |      25 |
|   1 |  NESTED LOOPS		     |	       |      1 |     14 |     14 |00:00:00.01 |      25 |
|   2 |   NESTED LOOPS		     |	       |      1 |     14 |     14 |00:00:00.01 |      11 |
|   3 |    TABLE ACCESS FULL	     | EMP     |      1 |     14 |     14 |00:00:00.01 |       7 |
|*  4 |    INDEX UNIQUE SCAN	     | PK_DEPT |     14 |      1 |     14 |00:00:00.01 |       4 |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     14 |      1 |     14 |00:00:00.01 |      14 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("E"."DEPTNO"="D"."DEPTNO")
23 rows selected.

执行计划中，离NESTED LOOPS 关键字最近的表就是驱动表。
EMP被扫描一次，返回14行数据。传值14次给被驱动表DEPT，DEPT被扫描了14次。
适用场景：
两表关联返回的数据量较小。
驱动表返回的数据量小，被驱动表走索引。
驱动表的选择的数据量指的是过滤之后返回的数据量。

2.HASH连接 HASH JOIN

之前说，返回数据量少走嵌套循环，那两表关联返回大量数据就应该走HASH连接。
算法：两表等值关联，返回大量数据，将较小的表选做驱动表，将驱动表的“select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的work area），对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。
示例：

SQL> select /*+ gather_plan_statistics use_hash(e,d) swap_join_inputs(d) */ e.ename,e.job,d.dname 
from emp e,dept d 
where e.deptno=d.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	732qdhmwavku3, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(e,d) */
e.ename,e.job,d.dname 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 | Reads  |	OMem |	1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	 1 |	    |	  14 |00:00:00.01 |	 13 |	   4 |	     |	     |		|
|*  1 |  HASH JOIN	   |	  |	 1 |	 14 |	  14 |00:00:00.01 |	 13 |	   4 |	1599K|	1599K|	979K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |	 1 |	  4 |	   4 |00:00:00.01 |	  6 |	   4 |	     |	     |		|
|   3 |   TABLE ACCESS FULL| EMP  |	 1 |	 14 |	  14 |00:00:00.01 |	  7 |	   0 |	     |	     |		|
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
21 rows selected.

同样的，靠近关键字的表是驱动表。
与嵌套循环不同的是，HASH连接的被驱动表只要扫描一次。
Used-Mem：表示HASH连接消耗了多少PGA，当驱动表太大，PGA不能完全容纳驱动表的时候，驱动表就会溢出到临时表空间，进而产生磁盘HASH连接，这时候HASH连接性能会严重下降。

3.排序合并连接 SORT MERGE JOIN

与HSAH连接只能处理两表等值关联不同。排序合并连接主要用于处理两表非等值关联，比如：>、>=、< 、<=、 <>。但是不能用于instr、substr、like、regexp_like关联，这些只能走嵌套循环。
算法：两表关联，先对两个表根据连接列进行排序，然后进行匹配。类似于嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存（pga中的的work area）匹配数据。
示例：

SQL> select /*+ gather_plan_statistics */ e.ename,e.job,d.dname from emp e,dept d where e.deptno >= d.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	8s8vryzyqwgkn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.ename,e.job,d.dname from emp
e,dept d where e.deptno >= d.deptno

Plan hash value: 844388907

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

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

   4 - access("E"."DEPTNO">="D"."DEPTNO")
       filter("E"."DEPTNO">="D"."DEPTNO")

4.笛卡尔连接 CARTESIAN JOIN

两个表没有连接条件的时候就会产生笛卡尔积，这种表连接方式就叫笛卡尔连接。

SQL> set autot trace
SQL> select * from emp,dept;
56 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	 56 |  3248 |	 10   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|	    |	 56 |  3248 |	 10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |	  4 |	 80 |	  3   (0)| 00:00:01 |
|   3 |   BUFFER SORT	     |	    |	 14 |	532 |	  7   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |	 14 |	532 |	  2   (0)| 00:00:01 |
-----------------------------------------------------------------------------