SQL 语句执行计划中的连接方式

🕗 发布于 2024-11-23 17:09 oracle sql 执行计划

SQL 语句执行计划中的连接方式

join操作

join操作基本分为3大类：外连接（细分为：左连接（Left outer join/ left join）、右连接（right outer join/ right join）、全连接（full outer join/ full join）)、自然连接(Natural join)、内连接(Inner Join/join)。

测试环境：

CREATE TABLE t01(id1 int,id2 int,id3 int);
CREATE TABLE t02(id1 int,id4 int,id5 int);
INSERT INTO t01 VALUES(1,1,1);
INSERT INTO t01 VALUES(2,2,2);
INSERT INTO t01 VALUES(3,3,3);

INSERT INTO t02 VALUES(1,4,4);
INSERT INTO t02 VALUES(2,2,2);
INSERT INTO t02 VALUES(4,6,6);
commit;
SELECT * FROM t01;
SELECT * FROM t02;

ITMS5_1@hfzcdb> SELECT * FROM t01;

       ID1        ID2        ID3
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3

ITMS5_1@hfzcdb> SELECT * FROM t02;

       ID1        ID4        ID5
---------- ---------- ----------
         1          4          4
         2          2          2
         4          6          6

内连接：

ITMS5_1@hfzcdb> SELECT * from t01 INNER JOIN t02 ON t01.id1=t02.id1;

       ID1        ID2        ID3        ID1        ID4        ID5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          4          4
         2          2          2          2          2          2
#或者：
ITMS5_1@hfzcdb> SELECT * from t01  JOIN t02 ON t01.id1=t02.id1;

       ID1        ID2        ID3        ID1        ID4        ID5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          4          4
         2          2          2          2          2          2

自然连接

自然连接是在两张表中寻找那些数据类型和列名都相同的字段,然后自动地将他们连接起来,并返回所有符合条件按的结果。

我们也可以将自然连接理解为内连接的一种。

ITMS5_1@hfzcdb> SELECT * from t01 Natural JOIN t02;

       ID1        ID2        ID3        ID4        ID5
---------- ---------- ---------- ---------- ----------
         1          1          1          4          4
         2          2          2          2          2

左外连接：

ITMS5_1@hfzcdb> SELECT * FROM t01 a LEFT OUTER JOIN t02 b ON a.id1=b.id1;

       ID1        ID2        ID3        ID1        ID4        ID5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          4          4
         2          2          2          2          2          2
         3          3          3

右外连接

ITMS5_1@hfzcdb> SELECT * FROM t01 a RIGHT OUTER JOIN t02 b ON a.id1=b.id1;

       ID1        ID2        ID3        ID1        ID4        ID5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          4          4
         2          2          2          2          2          2
                                          4          6          6

全外连接

ITMS5_1@hfzcdb> SELECT * FROM t01 a FULL OUTER JOIN t02 b ON a.id1=b.id1;   # outer可以省略

       ID1        ID2        ID3        ID1        ID4        ID5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          4          4
         2          2          2          2          2          2
                                          4          6          6
         3          3          3

交叉连接（笛卡尔积）

SELECT * FROM t01 CROSS JOIN t02;ITMS5_1@hfzcdb> SELECT * FROM t01 CROSS JOIN t02;

       ID1        ID2        ID3        ID1        ID4        ID5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          4          4
         1          1          1          2          2          2
         1          1          1          4          6          6
         2          2          2          1          4          4
         2          2          2          2          2          2
         2          2          2          4          6          6
         3          3          3          1          4          4
         3          3          3          2          2          2
         3          3          3          4          6          6
         
         
ITMS5_1@hfzcdb> select * from t01,t02;

       ID1        ID2        ID3        ID1        ID4        ID5
---------- ---------- ---------- ---------- ---------- ----------
         1          1          1          1          4          4
         1          1          1          2          2          2
         1          1          1          4          6          6
         2          2          2          1          4          4
         2          2          2          2          2          2
         2          2          2          4          6          6
         3          3          3          1          4          4
         3          3          3          2          2          2
         3          3          3          4          6          6

嵌套循环（Nested Loops）：简称NL

nestloop 适用于大小表关联小表做外表（驱动表，放内存中），外表（被驱动表）每返回一行数据，内表需要做一次全表扫描，该场景下适合再内表的关联键上建立索引，避免内表的多次全表扫描

select * 
  from 
  t_vio_violation v1,  t_vio_white_vehicle v2
  where
  v1.plate_nbr=v2.plate_nbr   and  v1.violation_time > sysdate -100
 
  Plan Hash Value  : 284138460 

-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |    1 |   886 |   15 | 00:00:01 |
|   1 |   NESTED LOOPS                 |                     |    1 |   886 |   15 | 00:00:01 |
|   2 |    NESTED LOOPS                |                     |   33 |   886 |   15 | 00:00:01 |
|   3 |     TABLE ACCESS FULL          | T_VIO_WHITE_VEHICLE |    1 |   359 |    2 | 00:00:01 |
| * 4 |     INDEX RANGE SCAN           | IDX_PLATE_NBR_01    |   33 |       |    2 | 00:00:01 |
| * 5 |    TABLE ACCESS BY INDEX ROWID | T_VIO_VIOLATION     |    8 |  4216 |   13 | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("V1"."PLATE_NBR"="V2"."PLATE_NBR")
* 5 - filter("V1"."VIOLATION_TIME">SYSDATE@!-100)

小表做外表（驱动表，放内存中）–T_VIO_WHITE_VEHICLE ，外表（被驱动表）–T_VIO_VIOLATION

遵循最上最右先执行的原则。

（归并）排序合并连接（Sort Merge Join）：SMJ

merge join 因为要排序，因此性能要差于hash join，若关联键上有索性，性能也不错，适用于关联键已有索引并且支持不等值连接 <= >=

【排序合并连接分为两个阶段】

1、Sort 阶段：两边集合按照连接字段进行排序。

2、Merge 阶段：排序好的两边集合进行相互合并（Merge）操作。

select  /*+ ordered use_merge(v2) */* 
  from 
  t_vio_violation v1,  t_vio_white_vehicle v2
    where
  v1.violation_time > sysdate -100
  and
   v1.plate_nbr=v2.plate_nbr
   
   
 Plan Hash Value  : 3221625541 

------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows    | Bytes      | Cost   | Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |       1 |        886 | 523280 | 00:00:41 |
|   1 |   MERGE JOIN          |                     |       1 |        886 | 523280 | 00:00:41 |
|   2 |    SORT JOIN          |                     | 6026069 | 3175738363 | 523277 | 00:00:41 |
| * 3 |     TABLE ACCESS FULL | T_VIO_VIOLATION     | 6026069 | 3175738363 | 103631 | 00:00:09 |
| * 4 |    SORT JOIN          |                     |       1 |        359 |      3 | 00:00:01 |
|   5 |     TABLE ACCESS FULL | T_VIO_WHITE_VEHICLE |       1 |        359 |      2 | 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("V1"."VIOLATION_TIME">SYSDATE@!-100)
* 4 - access("V1"."PLATE_NBR"="V2"."PLATE_NBR")
* 4 - filter("V1"."PLATE_NBR"="V2"."PLATE_NBR")

（散列）哈希连接（Hash Join）：简称HJ

hash join 仅适用于等值关联，两表中较小的表的关联键放内存中做hash散列再去窥探大表，性能较好

【哈希连接分为两个阶段】

1、Build 阶段：读取小表（Build Input）生成Hash表。 —构造阶段

2、Probe 阶段：读取大表（Probe Input）探查Hash表并进行连接。 --探查阶段

select  /*+ ordered use_hash(v2) */* 
  from 
  t_vio_violation v1,  t_vio_white_vehicle v2
    where
  v1.violation_time > sysdate -100
  and
   v1.plate_nbr=v2.plate_nbr
   
   Plan Hash Value  : 1360093866 

-----------------------------------------------------------------------------------------------
| Id  | Operation            | Name                | Rows    | Bytes      | Cost   | Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |       1 |        886 | 235876 | 00:00:19 |
| * 1 |   HASH JOIN          |                     |       1 |        886 | 235876 | 00:00:19 |
| * 2 |    TABLE ACCESS FULL | T_VIO_VIOLATION     | 6026027 | 3175716229 | 103631 | 00:00:09 |
|   3 |    TABLE ACCESS FULL | T_VIO_WHITE_VEHICLE |       1 |        359 |      2 | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("V1"."PLATE_NBR"="V2"."PLATE_NBR")
* 2 - filter("V1"."VIOLATION_TIME">SYSDATE@!-100)

三种连接方式比较

NL连接	sort Merge连接	Hash连接
海量数据连接慢	海量数据连接比较快	海量数据连接很快
特别依赖索引	不太依赖索引，有索引排序会快	不是太依赖索引，索引快速过滤出结果
随机方式扫描数据	不全是随机方式扫描数据	不全是随机方式扫描数据
从SGA的buffer cache读取数据	从PGA读取排序后的数据	从PGA读取Hash表数据
被驱动表需要扫描多次	outer表与inner表都只扫描一次	outer表与inner表都只扫描一次
不需要排序	需要排序的数据也是从buffer cache读取，不可避免	需要构建的数据也是从buffer cache读取，不可避免
	两个表都要排序	不需要排序
	两个表都要放到PGA，使用大量PGA 空间	只把小表放在PGA中

原文地址：https://blog.csdn.net/hf191850699/article/details/143976845

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：【SQL Server】华中农业大学空间数据库实验报告实验九触发器
下一篇：LeetCode 101题集（随时更新）

Windows电脑本地部署llamafile并接入Qwen大语言模型远程AI对话实战
Windows电脑本地部署llamafile并接入Qwen大语言模型远程AI对话实战
阅读更多2024-11-23
解决vm虚拟机中ctrl键粘滞问题（一直处于按下状态）
虚拟机中点击鼠标左键没有问题，单独按下键盘也没有问题，但是如果按下键盘的同时在按下鼠标左键就会卡住。而且100%稳定复现。发现问题原因：vm版本的问题，我的版本是17.5.0，升级成17.5.2 bu
阅读更多2024-11-23
第十章 JavaScript的应用
10.1.1 JavaScript简介1 简单性JavaScript 设计之初就考虑到了非专业程序员的使用，因此它的语法相对简单，容易上手。2 动态性JavaScript 是一种解释型语言，代码可以在
阅读更多2024-11-23
非root用户安装CUDA
4.下载完成后执行.run文件，需要添加两个参数，一个是--override，用于跳过某些检查或限制；另一个是--toolkitpath，它表示安装路径，必须是自己目录下面的路径，如果是根目录就会安装
阅读更多2024-11-23
Python操作neo4j库py2neo使用之py2neo 删除及事务相关操作（三）
【代码】## Python操作neo4j库py2neo使用之py2neo 删除及事务相关操作（三）
阅读更多2024-11-23
【通俗理解】Jensen不等式与变分分布q(z)在积分计算中的应用
Jensen不等式 #变分分布 #积分计算 #期望 #凸函数 #优化问题 #下界估计 #机器学习。
阅读更多2024-11-23
「Mac玩转仓颉内测版27」基础篇7 - 字符串类型详解
本篇将介绍 Cangjie 中的字符串类型，包括字符串的定义、字面量形式、插值表达、常用操作及应用场景，帮助开发者熟练掌握字符串的使用。
阅读更多2024-11-23
数据科学与SQL：组距分组分析 | 区间分布问题
绝对值分布分析也可以理解为组距分组分析。对于某个指标而言，一个记录对应的指标值的绝对值，肯定落在所有指标值的绝对值的最小值和最大值构成的区间内，根据一定的算法，在把这个区间划分为等距离的几个小区间，，
阅读更多2024-11-23
c++编译报C1004错误的原因及解决办法
可能是你的代码文件最后没有正确的结束符，或者是缺少了一些必要的头文件或语句。默认磁盘驱动器没有足够的空间用于临时文件，需要大约两倍于源文件的空间。编译器到达了源文件尾但未解析构造。计算结果为假的 #i
阅读更多2024-11-23
mysql 去重补全取出重复变量函数和存储过程
mysql 去重补全取出重复变量函数和存储过程
阅读更多2024-11-23

SQL 语句执行计划中的连接方式