【Oracle篇】SQL执行计划之多表连接(含内连接、外连接、半连接、反连接、笛卡尔连接五种连接方式和嵌套、哈希、排序合并三种连接算法)（第四篇，总共七篇）
💫《博主介绍》：✨又是一天没白过，我是奈斯，DBA一名✨
💫《擅长领域》：✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux，也在扩展大数据方向的知识面✌️
💖💖💖大佬们都喜欢静静的看文章，并且也会默默的点赞收藏加关注💖💖💖
哈喽各位集帅，转眼间就进入十月了（时光如梭一年又要结束了😭）。十月份的北京，天气开始有了些凉意，算是从夏末秋初慢慢过渡到深秋的阶段。早晚的温度会明显低很多，有时候早晨起来能感觉到空气里带着那么一丝丝寒气，得赶紧翻出长袖衣服甚至是薄毛衣来穿上。白天虽然太阳还是挺大，但比起夏天那种炙烤感，这时候的阳光就显得温和多了，晒在身上挺舒服的。
冬天太冷，让人只想蜷缩在温暖的角落；夏天太热，热得人心浮气躁，难以集中精力。博主最喜欢的就是当前的月份，不冷不热。这个时候的天气，既没有了夏日的酷热，也还未迎来冬日的严寒，正是静下心来写写博客、整理思绪的最佳时节。
那么今天作为执行计划的第四篇章，和大家深入探讨一个在实际业务场景中极为常见且至关重要的操作——多表连接。在实际的业务操作中，经常需要将多个表的数据关联起来进行查询或分析，这就是多表连接，表与表之间有那几种连接方式和连接时的算法是怎么选择的是非常值得深入学习的一个话题，那么今天带着这个话题，让我们开始今天的内容吧。还是老规矩为了让大家更容易消化和逐个理解，我将分成七篇文章来进行介绍，以便大家劳逸结合而不至于感觉到阅读枯燥，七篇的内容分别如下：
- 第一篇：统计信息和动态采样的深度剖析
- 第二篇：全面理解优化器和SQL语句的解析步骤(含执行计划的详细分析和四种查看方式)
- 第三篇：SQL执行计划之访问路径(含表级别、B树索引、位图索引、簇表四大类访问路径)
- 第四篇：SQL执行计划之多表连接(含内连接、外连接、半连接、反连接、笛卡尔连接五种连接方式和嵌套、哈希、排序合并三种连接算法)（当前篇）
- 第五篇：精细化查询优化：如何有效使用Hint干预执行计划
- 第六篇：掌握SQL Tuning Advisor优化工具：从工具使用到SQL优化的全方位指南
- 第七篇：SQL性能优化实战案例(从15秒优化到0.08秒)
目录
案例一：（归并）排序合并连接（sort merge join）SMJ
官方文档对多表连接的详细介绍（12c版本）：
在开始介绍连接方式和连接算法之前我们先搞清关于多表连接的原理，只有先搞明白多表之间是怎么连接的才能更好的学习之后的内容。
连接介绍：
连接是将来自两个行源（如表或视图）的输出组合在一起，并返回一个行源。返回的行源是数据集。
连接的特点是在WHERE（非ANSI）条件中有多个表，或者使用FROM ... JOIN(ANSI)语句中有多个表。只要FROM子句中存在多个表，Oracle数据库就会执行联接。
连接时使用表达式比较两个行源，表达式就是表和表直接的连接条件，表和表直接的连接条件可以是等值连接（表之间关联式为等于号），也可以是非等值连接（表之间关联式为<>!=或者between）。连接条件定义了表之间的关系，如果语句没有指定连接条件，则数据库将执行笛卡尔连接，将一个表中的每一行与另一个表的每一行都进行匹配（关于笛卡尔连接下面会详细介绍哦）。
下面我用树状结构来讲解一下不同的连接方式的匹配数据原理。
Join Tree
如上图所示，表1是左表，表2是右表。优化器从左到右处理连接。例如，如果此图描述了嵌套循环联接（nested loops join），则表1是外部循环（outer loop），表2是内部循环（inner loop）。
Left Deep Join Tree
连接的输入是前一个连接的结果集。如果联接树的每个内部节点的右子节点都是表，则该树是左深度联接树（left deep join tree），如上图所示。大多数连接树都是左深度连接（left deep join tree）。
Right Deep Join Tree
如果联接树的每个内部节点的左子节点都是一个表，则该树称为右深度联接树（right deep join tree），如上图所示。
Bushy Join Tree
如果一棵连接树的内部节点的左子节点或右子节点可以是一个连接节点，那么该树称为浓密连接树（bushy join tree）。在上面的示例中，table4是联接节点的右子节点，table1是联接节点的左子节点，table2是联接节点的左子节点，连接的两个输入都是先前连接的结果。
连接方式的匹配数据原理我用树状结构通俗易懂都介绍了一遍，可能有些小伙伴还是会很懵逼，不过没有关系，到了下面连接方式的介绍我会用案例的形式再介绍一下，等看完案例再回来看树状结构这些内容时会有一种柳暗花明又一村的感觉
优化器如何执行连接语句：
数据库连接成对的行源。当FROM子句中存在多个表时，优化器必须确定每个表对哪种连接操作最有效。
优化器必须做出下表所示的相互关联的决策。
连接操作 描述 访问路径（Access paths） 对于简单语句，优化器必须选择一个访问路径来从联接语句中的每个表检索数据。例如，优化器可能会在全表扫描或索引扫描之间进行选择。 连接算法（Join methods） 要连接每对行源，Oracle数据库必须决定如何连接。“如何”是连接方式。可能的连接方法有嵌套循环（nested loop）、排序合并（sort merge）和哈希连接（hash joins）。笛卡尔连接需要上述连接方法之一。每种连接方法都有特定的情况，在这些情况下，它比其他方法更合适。 连接方式（Join types） 连接条件决定了连接方式。例如，内部联接（inner join）仅检索符合联接条件的行，外部联接（outer join）检索不符合联接条件的行。 连接顺序（2个以上的表连接，Join order） 要执行连接两个以上表的语句，Oracle数据库连接两个表，然后将生成的行源连接到下一个表。此过程将继续，直到所有表都连接到结果中。例如，数据库连接两个表，然后将结果连接到第三个表，再将此结果连接到一个第四个表，以此类推。
优化器如何为连接选择执行计划：
在确定连接顺序和连接算法时，优化器的目标是尽早减少行数，以便在整个SQL语句执行过程中执行更少的工作。
优化器根据可能的连接顺序、连接算法和可用访问路径生成一组执行计划。然后，优化器估计每个计划的成本，并选择成本最低的计划。在选择执行计划时，优化器会考虑以下因素：
- 优化器首先确定连接两个或多个表是否会导致行源最多包含一行：优化器根据表上的UNIQUE和PRIMARY KEY约束来识别这种情况。如果存在这种情况，优化器会将这些表放在连接顺序的第一位。然后，优化器优化剩余表集的连接。
- 对于具有外部联接条件的联接语句，在联接顺序中，具有外部联接运算符的表通常位于条件中的另一个表之后：一般来说，优化器不会考虑违反此准则的连接顺序，尽管在某些情况下优化器会覆盖此排序条件。同样，当子查询被转换为反连接或半连接时，子查询中的表必须位于它们所连接或关联的外部查询块中的表之后。然而，在某些情况下，哈希反连接和半连接能够覆盖此排序条件。
优化器通过计算估计的I/O和CPU来估计查询计划的成本。这些I/O具有与之相关的特定成本：单个块I/O的成本和多块I/O的另一个成本。此外，不同的函数和表达式都有与之相关的CPU成本。优化器使用这些指标确定查询计划的总成本。这些指标可能会受到编译时许多初始化参数和会话设置的影响，例如DB_FILE_MULTI_BLOCK_READ_COUNT设置、系统统计信息等。例如，优化器通过以下方式估算成本：
- nested loops join：嵌套循环连接的成本取决于将外部表（驱动表）的每个选定行及其内部表（被驱动表）的每个匹配行读取到内存中的成本。优化器使用数据字典中的统计数据来估计这些成本。
- sort merge join：排序合并连接的成本在很大程度上取决于将所有源读取到内存中并对其进行排序的成本。
- hash join：哈希连接的成本在很大程度上取决于在连接的一个输入端构建哈希表并使用连接另一端的行进行探测的成本。
一、多表连接算法介绍
连接算法是联接两个表的机制。根据统计信息，优化器选择估计成本最低的方法。如下图所示，每个连接算法都有两个子方法：驱动表（也称为外部表）和被驱动表（也称为内部表）。
1、嵌套循环连接（ Nested Loops Joins）
嵌套循环是将外部数据集连接到内部数据集，快速取出第一行。逐条取出条件的数据，对比关联表。对于外部数据集中与单个表谓词匹配的每一行，数据库将检索内部数据集中满足联接谓词的所有行。如果索引可用，则数据库可以使用它通过rowid访问内部数据集。
优化器何时考虑嵌套循环连接：
嵌套循环连接在以下情况下非常有用：数据库连接小数据子集，数据库连接大数据集，优化器模式设置为FIRST_ROWS，或者连接条件是访问内部表的有效方法。注意：驱动优化器决策的是连接预期的行数，而不是底层表的大小。例如，一个查询可能会连接两个各有10亿行的表，但由于过滤器的原因，优化器期望每个表有5行的数据集。
一般来说，嵌套循环连接在具有连接条件索引的小表上效果最佳。如果一个行源只有一行，就像对主键值进行相等查找一样（例如，WHERE employee_id=101），那么连接就是一个简单的查找。优化器总是试图将最小的行源放在第一位，使其成为驱动表。
优化器决定使用嵌套循环时，会考虑各种因素。例如，数据库可以在批处理中从外部行源读取几行。根据检索到的行数，优化器可以选择嵌套循环或哈希连接到内部行源。例如，如果一个查询将部门与驱动表员工联系起来，并且谓词在employees.last_name中指定了一个值，那么数据库可能会在last_name的索引中读取足够的条目，以确定是否超过内部阈值。如果未通过阈值，则优化器会选择嵌套循环连接到部门，如果通过阈值，数据库会执行哈希连接，这意味着读取其余员工，将其哈希到内存中，然后连接到部门。
如果内环的访问路径不依赖于外环，则结果可以是笛卡尔积：对于外环的每次迭代，内环都会产生相同的行集。为了避免此问题，请使用其他连接方法连接两个独立的行源。
嵌套循环连接的工作原理：
从概念上讲，嵌套循环相当于两个嵌套的for循环。例如，如果一个查询连接了员工和部门，那么代码中的嵌套循环可能是：
FOR erow IN (select * from employees where X=Y) LOOP FOR drow IN (select * from departments where erow is matched) LOOP output values from erow and drow END LOOP END LOOP对外循环的每一行执行内环。employees表是“外部”数据集，因为它位于外部for循环中。外部表也称驱动表。departments表是“内部”数据集，因为它位于for循环的内部。
嵌套循环连接涉及以下基本步骤：
- 优化器确定驱动表并将其指定为外循环：外部循环产生一组用于驱动连接条件的行，表可以是使用索引扫描、全表扫描或任何其他生成行的操作访问的表。内循环的迭代次数取决于在外循环中检索到的行数。例如，如果从外部表检索到10行，则数据库必须在内部表中执行10次查找。如果从外部表检索到10000000行，则数据库必须在内部表中执行10000000次查找。
- 优化器将另一个表指定为内部循环：在执行计划中，外部循环出现在内部循环之前，如下所示。
NESTED LOOPS outer_loop inner_loop
- 对于来自客户端的每个获取请求，基本过程是：第一步：从外部行源获取行；第二步：探测内部行源以查找与谓词条件匹配的行；第三步：重复上述步骤，直到获取请求获得所有行。有时，数据库会对行ID进行排序，以获得更有效的缓冲区访问模式。
如果driving row source（外部表，驱动表）比较小，并且在innerrowsource（内部表，被驱动表）上有唯一索引，或有高选择性非唯一索引时，使用嵌套循环连接可以得到较好的效率。并且NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。
案例一：嵌套循环（Nested Loops）NL
create table itpux11(name varchar2(12),sex varchar2(6)); create table itpux12(name varchar2(12),grade number(3)); insert into itpux11 values('itpux01','男'); insert into itpux11 values('itpux02','男'); insert into itpux11 values('itpux03','女'); insert into itpux11 values('itpux04','女'); insert into itpux11 values('itpux05','女'); commit; insert into itpux12 values('itpux01','20'); insert into itpux12 values('itpux02','21'); insert into itpux12 values('itpux03','18'); insert into itpux12 values('itpux04','19'); insert into itpux12 values('itpux055','28'); commit; create index idx_itpux11_name on itpux11(name); create index idx_itpux12_name on itpux12(name); alter session set optimizer_mode=rule; select itpux11.sex,itpux12.grade from itpux11,itpux12 where itpux11.name=itpux12.name;优化器目标选择：规则
第一步TABLE ACCESS SCAN：全表扫描itpux12表
第二步INDEX RANGE SCAN：使用idx_itpux11_name索引
第三步Nested Loops：逐条取出条件的数据，对比关联表。如：返回10行，那么循环10次
第四步TABLE ACCESS BY INDEX ROWID：取出条件内数据的rowid
2、哈希连接（Hash Joins）
哈希连接适合大型结果集的处理。优化器使用两个数据集中较小的一个在内存中的连接键上构建哈希表，使用确定性哈希函数指定哈希表中存储每一行的位置。然后，数据库扫描较大的数据集，探测哈希表以找到符合连接条件的行。
优化器何时考虑哈希连接：
一般来说，当必须连接相对大量的数据（或必须连接一个小表的很大一部分）并且连接是等连接时，优化器会考虑哈希连接。
当较小的数据集适合内存时，哈希连接最具成本效益。在这种情况下，成本仅限于对两个数据集进行一次读取。
由于哈希表位于PGA中，Oracle数据库可以访问行而无需锁存它们。此技术通过避免重复锁存和读取数据库缓冲区缓存中的块来减少逻辑I/O。
如果数据集不适合内存，则数据库会对行源进行分区，然后逐个分区进行连接。这可能会使用大量的排序区域内存和临时表空间的I/O。这种方法仍然是最具成本效益的，特别是在数据库使用并行查询服务器的情况下。
当哈希表不适合PGA时，哈希连接是如何工作的
当哈希表不完全适合PGA时，数据库必须使用不同的技术。在这种情况下，数据库使用临时空间来保存哈希表的部分（称为分区），有时还保存探测哈希表的较大表的部分。
基本流程如下：
- 数据库对较小的数据集进行全面扫描，然后在PGA和磁盘上构建一个哈希桶数组：当PGA哈希区域填满时，数据库会在哈希表中找到最大的分区，并将其写入磁盘上的临时空间。数据库将属于该磁盘分区的任何新行存储在磁盘上，并将所有其他行存储在PGA中。因此，哈希表的一部分在内存中，一部分在磁盘上。
- 数据库首先读取其他数据集。对于每一行，数据库都会执行以下操作：一、将相同的哈希函数应用于一个或多个连接列，以计算相关哈希桶的数量；二、探测哈希表以确定内存中的bucket中是否存在行。如果散列值指向内存中的一行，则数据库完成连接并返回该行。但是，如果该值指向磁盘上的哈希分区，则数据库将使用与原始数据集相同的分区方案将此行存储在临时表空间中。
- 数据库逐一读取磁盘上的每个临时分区
- 数据库将每个分区行连接到相应磁盘上临时分区中的行。
哈希连接的工作原理：
哈希算法接受一组输入，并应用确定性哈希函数来生成1到n之间的哈希值，其中n是哈希表的大小。
在哈希连接中，输入值是连接键。输出值是哈希表数组中的索引（槽）。
为了说明构建的哈希表，假设数据库在部门和雇员的连接中散列hr.departments。连接键列是department_id。前5行部门如下：
SQL> select * from departments where rownum < 6; DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500数据库将散列函数应用于表中的每个department_id，为每个id生成一个散列值。对于这个例子，散列表有5个槽(可以更多或更少)。因为n是5，所以可能的哈希值范围是从1到5。哈希函数可能会为部门id生成以下值：
f(10) = 4 f(20) = 1 f(30) = 4 f(40) = 2 f(50) = 5注意，散列函数恰好为部门10和30生成相同的散列值4。这就是所谓的哈希冲突。在这种情况下，数据库使用一个链表将部门10和30的记录放在同一个槽中。从概念上讲，哈希表如下所示：
1 20,Marketing,201,1800 2 40,Human Resources,203,2400 3 4 10,Administration,200,1700 -> 30,Purchasing,114,1700 5 50,Shipping,121,1500哈希连接在Oracle 7版本后引入，使用了比较先进的连接理论，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数（9i后自动管理），才能取得较好的性能。在2个较大的row source之间连接时会取得相对较好的效率，在一个rowsource较小时则能取得更好的效率。需要注意只能用于等值连接中
案例一：（散列）哈希连接（Hash Join）HJ
select * from itpux_m10 i1,itpux_yg i2 where i1.name=i2.name; ---itpux_m10有10万行数据，itpux_yg有8千行数据。并且进行了多表联合查询第一步TABLE ACCESS SCAN：全表扫描itpux_yg表
第一步TABLE ACCESS SCAN：全表扫描itpux_m10表
第三步HASH JOIN：大表的数据逐个与小表进行匹配关联。
小表itpux_YG为驱动表，并且离nested loops，hash join近
3、排序合并连接（Sort Merge Joins）
排序合并连接是嵌套循环连接的变体，在非等值连接中有效(> < !=)。如果联接中的两个数据集尚未排序，则数据库会对其进行排序。这些是SORT JOIN操作。对于第一个数据集中的每一行，数据库会探测第二个数据集中匹配的行，并根据前一次迭代中的匹配来连接它们。这是MERGE JOIN操作。
图中的顶部显示了一个MERGE JOIN框。在左侧，一个SORT JOIN框指向MERGE JOIN。“First Row Source”指向SORT JOIN。在右侧，一个SORT JOIN框指向MERGE JOIN。“Second Row Source”指向SORT JOIN。
优化器何时考虑排序合并连接：
哈希连接需要一个哈希表和一个对该表的探测，而排序合并连接需要两个排序。
当以下任何条件为真时，优化器可以选择排序合并连接而不是哈希连接来连接大量数据：
- 两个表之间的连接条件不是等连接，即使用不等式条件，如<、<=、>或>=：与排序合并相反，哈希连接需要相等条件。
- 由于其他操作需要排序，优化器发现使用排序合并成本更低：如果存在索引，则数据库可以避免对第一个数据集进行排序。但是，数据库总是对第二个数据集进行排序，而不管索引如何。
排序合并与嵌套循环连接相比具有与哈希连接相同的优势：数据库访问PGA中的行而不是SGA中的行，通过避免重复锁存和读取数据库缓冲区缓存中的块来减少逻辑I/O。一般来说，哈希连接比排序合并连接性能更好，因为排序成本很高。然而，与哈希连接相比，排序合并连接具有以下优势：
- 初始排序之后，合并阶段被优化，从而更快地生成输出行。
- 当哈希表不完全适合内存时，排序合并可能比哈希连接更具成本效益：内存不足的哈希连接需要将哈希表和其他数据集复制到磁盘。在这种情况下，数据库可能需要多次从磁盘读取。在排序合并中，如果内存无法容纳这两个数据集，则数据库会将它们都写入磁盘，但读取每个数据集的次数不超过一次。
排序合并连接的工作原理：
与嵌套循环连接一样，排序合并联接读取两个数据集，但在它们尚未排序时对它们进行排序。
对于第一个数据集中的每一行，数据库在第二个数据集中找到一个起始行，然后读取第二个数据集，直到找到一个不匹配的行。在代码中，排序合并的高级算法可能如下所示：
READ data_set_1 SORT BY JOIN KEY TO temp_ds1 READ data_set_2 SORT BY JOIN KEY TO temp_ds2 READ ds1_row FROM temp_ds1 READ ds2_row FROM temp_ds2 WHILE NOT eof ON temp_ds1,temp_ds2 LOOP IF ( temp_ds1.key = temp_ds2.key ) OUTPUT JOIN ds1_row,ds2_row ELSIF ( temp_ds1.key <= temp_ds2.key ) READ ds1_row FROM temp_ds1 ELSIF ( temp_ds1.key => temp_ds2.key ) READ ds2_row FROM temp_ds2 END LOOP对于非等值连接，这种连接方式的效率是比较高的。如果在关联的列上都有索引，效果更好。对于将2个较大的rowsource做连接，该连接方法比NL连接要好一些。但是如果sortmerge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。
案例一：（归并）排序合并连接（sort merge join）SMJ
select i1.name, i2.name from itpux_member i1, itpux_yg i2 where i1.name <> i2.name and i1.id < i2.jobid; ----itpux_m10有1千万行数据，itpux_yg有8千行数据。并且进行了多表联合查询
4、连接算法的区别总汇：
总结：效率上哈希连接>嵌套连接>排序合并连。这三种连接方式主要是为了Join操作：内连接、外连接(细分为：左连接、右连接、全连接)、半连接、反连接、笛卡尔连接
处理方式：
嵌套循环连接：对于驱动表（小表）的每一行，都会与被驱动表（大表）进行逐行匹配。这意味着需要依次扫描驱动表和被驱动表，并通过比较连接列的值来找到匹配的行。嵌套循环连接通常会利用索引来提高查询性能。
哈希连接：首先将驱动表和被驱动表的连接列进行哈希处理，并将它们存储在内存中的哈希表中。然后，通过比较连接列的哈希值来查找匹配的行。哈希连接适合处理大型数据集，但消耗较多的内存。
排序合并连接：对于两个表，都会按照连接列进行排序操作。然后，通过比较连接列的值将匹配的行组合在一起。排序合并连接通常用于已经按连接列排序的情况，不需要额外的内存。
内存需求：
嵌套循环连接：由于逐行匹配的方式，所需的内存较小。
哈希连接：需要较大的内存空间来存储哈希表，特别是处理大型数据集时。
排序合并连接：如果表已经按连接列排序，则不需要额外的内存。但如果需要进行排序操作，可能需要使用临时表空间。
性能影响：
嵌套循环连接：适用于一个表较小，另一个表较大且有合适的索引的情况。但在两个大表连接时，性能可能较差。
哈希连接：适用于大型数据集，可以利用哈希表在内存中快速查找匹配的行。但如果内存不足，可能会涉及磁盘读写操作，性能可能受到影响。
排序合并连接：适用于已经按连接列排序的情况，不需要额外的内存。但如果需要进行排序操作，可能会产生较高的开销。
好啦，关于多表连接算法的内容到这里就结束了！🎉 涵盖了嵌套连接🔄、哈希连接🔍 和排序合并连接📈 这三种强大的算法。每一种算法都有其独特的魅力和适用场景，希望各位集帅细细品味并有所收获！✨
二、多表连接方式介绍
连接方式由连接条件的类型决定。
1、内连接（Inner Joins）
内连接（有时称为简单连接，simple join）是一种只返回满足连接条件的行的连接。内部连接要么是等值连接要么是非等值连接。
内连接之等值连接
相等联接是一种内部联接，其联接条件包含相等运算符。以下示例是一个等值联接，因为联接条件只包含一个相等运算符：
SELECT e.employee_id, e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id;在前面的查询中，连接条件为e.department_id=d.department_id。如果employees表中department_id（部门ID）字段的某一行与departments表中同样department_id（部门ID）字段的某一行值匹配，则数据库返回联接结果。否则，数据库不会返回结果。
内连接之非等值连接
非等值联接是一种内部联接，其联接条件包含一个不是相等运算符的运算符。以下查询列出了雇员176号（由于他在2007年换了工作，所以列在job_history表中）在该公司工作时雇佣日期发生的所有雇员：
SELECT e.employee_id, e.first_name, e.last_name, e.hire_date FROM employees e, job_history h WHERE h.employee_id = 176 AND e.hire_date BETWEEN h.start_date AND h.end_date;在前面的示例中，连接employees和job_history表的条件不包含相等运算符，因此它是一个非相等联接。
需要注意，hash join至少需要部分等值连接。以下SQL脚本包含一个等式连接条件（e1.empno = e2.empno）和一个非等值连接（e1.hiredate BETWEEN e2.hiredate-1 AND e2.hiredate+1）
SET AUTOTRACE TRACEONLY EXPLAIN SELECT * FROM scott.emp e1 JOIN scott.emp e2 ON ( e1.empno = e2.empno AND e1.hiredate BETWEEN e2.hiredate-1 AND e2.hiredate+1 )优化器为查询选择一个哈希连接，执行计划如下图所示：
Execution Plan ---------------------------------------------------------- Plan hash value: 3638257876 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 174 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN | | 1 | 174 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("E1"."EMPNO"="E2"."EMPNO") filter("E1"."HIREDATE">=INTERNAL_FUNCTION("E2"."HIREDATE")-1 AND "E1"."HIREDATE"<=INTERNAL_FUNCTION("E2"."HIREDATE")+1)
2、外连接（Outer Joins）
外连接返回满足联接条件的所有行，以及一个表中没有满足条件的行。因此，外连接的结果集是内连接的超集。
在ANSI语法中，OUTER JOIN子句指定了一个外部连接。在FROM子句中，左表出现在OUTER JOIN关键字的左侧，右表出现在这些关键字的右侧。左表也称为外表，右表也称为内表。例如，在以下语句中，employees表是左侧或外部表：
SELECT employee_id, last_name, first_name FROM employees LEFT OUTER JOIN departments ON (employees.department_id=departments.departments_id);外连接要求外连接的表是驱动表。在前面的示例中，employees是驱动表，departments是驱动表。
外连接之嵌套循环
数据库使用该操作在两个表之间的外部连接中循环。外部联接返回外部(保留的)表行，即使内部(可选)表中没有相应的行。
在标准的嵌套循环中，优化器根据成本选择表的顺序——哪个是驱动表，哪个是被驱动表。但是，在嵌套循环外部联接中，联接条件决定了表的顺序。数据库使用外部的行保留表驱动到内部表。
在下列情况下，优化程序使用嵌套循环联接来处理外部联接：
- 可以从outer table到inner table。
- 数据量足够低，使得嵌套循环方法有效。
对于嵌套循环外连接的示例，可以在SQL中添加USE_NL提示，以指示优化器使用嵌套循环。例如：
SELECT /*+ USE_NL(c o) */ cust_last_name, SUM(NVL2(o.customer_id,0,1)) "Count" FROM customers c, orders o WHERE c.credit_limit > 1000 AND c.customer_id = o.customer_id(+) GROUP BY cust_last_name;
外连接之hash连接
当数据量大到足以使散列连接有效时，或者不可能从外部表驱动到内部表时，优化器使用散列连接来处理外部连接。
成本决定了表的顺序。包括保留行的外部表可用于构建哈希表，或者可用于探测哈希表。
此示例显示了一个典型的散列连接外部连接查询及其执行计划。在本例中，查询了信用限额大于1000的所有客户。需要一个外部联接，以便查询能够捕获没有订单的客户。
- outer table是customers。
- inner table是orders。
- 该连接保留customers表的行，包括那些在orders表中没有对应行的行。
可以使用NOT EXISTS子查询来返回行。但是，因为查询的是表中的所有行，所以hash连接的性能会更好(除非NOT EXISTS子查询没有嵌套)。
SELECT cust_last_name, SUM(NVL2(o.customer_id,0,1)) "Count" FROM customers c, orders o WHERE c.credit_limit > 1000 AND c.customer_id = o.customer_id(+) GROUP BY cust_last_name; --------------------------------------------------------------------------- | Id | Operation | Name |Rows |Bytes|Cost (%CPU)|Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7 (100)| | | 1 | HASH GROUP BY | | 168 | 3192 | 7 (29)| 00:00:01 | |* 2 | HASH JOIN OUTER | | 318 | 6042 | 6 (17)| 00:00:01 | |* 3 | TABLE ACCESS FULL| CUSTOMERS | 260 | 3900 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| ORDERS | 105 | 420 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID") PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- 3 - filter("C"."CREDIT_LIMIT">1000) 4 - filter("O"."CUSTOMER_ID">0)该查询查找满足各种条件的客户。当外连接在内部表中找不到任何相应的行时，它将为内部表列和外部(保留的)表行返回NULL。该操作查找没有任何
orders行的所有customers行。
外连接之排序合并连接
当外部联接不能从外部(保留)表驱动到内部(可选)表时，它不能使用散列联接或嵌套循环联接。
在这种情况下，它使用排序合并外部联接。
在下列情况下，优化程序对外连接使用排序合并：
- 嵌套循环连接是低效的。由于数据量的原因，嵌套循环联接可能效率低下。
- 优化器发现使用排序合并比散列连接更便宜，因为其他操作需要排序。
3、半连接（Semi joins）
半连接是两个数据集之间的连接，当子查询数据集中存在匹配行时，它返回第一个数据集中的一行。
数据库在第一次匹配时停止处理第二个数据集。因此，当第二个数据集中的多行满足子查询条件时，优化不会复制第一个数据集中的行。小提示💥：半连接和反连接被认为是连接类型，即使导致它们的SQL构造是子查询。它们是内部算法，优化器使用这些算法来展平子查询构造，以便能够以类似连接的方式解析它们。
半连接如何工作：
根据所使用的连接类型，半连接优化的实现方式会有所不同。
优化器如何影响半连接：
当查询只需要确定是否存在匹配时，半连接可以避免返回大量的行。
对于大型数据集，这种优化可以大大节省嵌套循环连接的时间，嵌套循环连接必须遍历内部查询为外部查询中的每一行返回的每一条记录。优化器可以将半连接优化应用于嵌套循环连接、哈希连接和排序合并连接。
在下列情况下，优化器会选择进行半连接：
- 该语句使用IN或EXISTS子句。
- 该语句在in或EXISTS子句中包含一个子查询。
- IN或EXISTS子句不包含在or分支内。
根据所使用的连接方式，半连接优化的实现方式会有所不同。以下代码显示了嵌套循环连接的半连接：
FOR ds1_row IN ds1 LOOP match := false; FOR ds2_row IN ds2_subquery LOOP IF (ds1_row matches ds2_row) THEN match := true; EXIT -- stop processing second data set when a match is found END IF END LOOP IF (match = true) THEN RETURN ds1_row END IF END LOOP在上面的代码中，ds1是第一个数据集，ds2_subquery是子查询数据集。代码从第一个数据集中获取第一行，然后循环子查询数据集寻找匹配项。代码在找到匹配项后立即退出内部循环，然后开始处理第一个数据集中的下一行。
使用WHERE EXISTS进行半连接
以下查询使用WHERE EXISTS子句仅列出包含员工的部门：
SELECT department_id, department_name FROM departments WHERE EXISTS (SELECT 1 FROM employees WHERE employees.department_id = departments.department_id)执行计划揭示了步骤1中的NESTED LOOPS SEMI操作：
--------------------------------------------------------------------------- | Id| Operation | Name |Rows|Bytes|Cost (%CPU)|Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | NESTED LOOPS SEMI | |11 | 209 | 2 (0)|00:00:01 | | 2 | TABLE ACCESS FULL| DEPARTMENTS |27 | 432 | 2 (0)|00:00:01 | |*3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |44 | 132 | 0 (0)| | ---------------------------------------------------------------------------
4、反连接（Anti joins）
反联接是两个数据集之间的联接，当子查询数据集中不存在匹配行时，它从第一个数据集返回一行。
像半连接一样，当找到第一个匹配时，反连接停止处理子查询数据集。与半连接不同，反连接只在找不到匹配时返回一行。
优化器如何影响反连接：
当查询只需要在不存在匹配时返回一行时，反联接可以避免不必要的处理。
对于大型数据集，这种优化可以大大节省嵌套循环连接的时间。对于外部查询中的每一行，后一个连接必须遍历内部查询返回的每一条记录。优化器可以将反联接优化应用于嵌套循环联接、哈希联接和排序合并联接。
在以下情况下，优化器可能会选择反连接：
- 该语句使用NOT IN或NOT EXISTS子句。
- 该语句在NOT in或NOT EXISTS子句中有一个子查询。
- NOT IN或NOT EXISTS子句不包含在or分支中。
- 该语句执行外部连接，并将IS NULL条件应用于连接列，如下例所示：
SET AUTOTRACE TRACEONLY EXPLAIN SELECT emp.* FROM emp, dept WHERE emp.deptno = dept.deptno(+) AND dept.deptno IS NULL Execution Plan ---------------------------------------------------------- Plan hash value: 1543991079 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)|Time | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 1400 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 14 | 1400 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO") Note ----- - dynamic statistics used: dynamic sampling (level=2)
反连接如何工作：
根据所使用的连接类型，反连接优化的实现方式会有所不同。 以下代码显示了嵌套循环联接的反联接：
FOR ds1_row IN ds1 LOOP match := true; FOR ds2_row IN ds2 LOOP IF (ds1_row matches ds2_row) THEN match := false; EXIT -- stop processing second data set when a match is found END IF END LOOP IF (match = true) THEN RETURN ds1_row END IF END LOOP在前面的伪码中，ds1是第一个数据集，ds2是第二个数据集。该代码从第一个数据集中获得第一行，然后循环遍历第二个数据集寻找匹配项。一旦找到匹配项，代码就会退出内部循环，并开始处理第一个数据集中的下一行。
5、笛卡尔连接（Cartesian Joins）
当一个或多个表与语句中的任何其他表没有任何联接条件时，数据库使用的就是笛卡尔连接。
优化器将一个数据源的每一行与另一个数据来源的每一行都连接起来，创建两个集合的笛卡尔积。因此，连接产生的总行数是使用以下公式计算的，其中rs1是第一行集中的行数，rs2是第二行集的行数：
rs1 X rs2 = total rows in result set
优化器考虑笛卡尔连接时：
优化器仅在特定情况下对两个表使用笛卡尔连接。通常，情况如下：
- 不存在连接条件：在某些情况下，优化器可以选择两个表之间的公共过滤条件作为可能的连接条件。注意：如果查询计划中出现笛卡尔连接，则可能是由于无意中省略了连接条件造成的。一般来说，如果一个查询连接n个表，那么需要n-1个连接条件来避免笛卡尔连接。
- ORDERED提示在指定连接表之前指定一个表。
笛卡尔连接如何工作：
笛卡尔连接使用嵌套的FOR循环。 概括地说，笛卡尔连接的算法如下所示，其中ds1通常是较小的数据集，ds2是较大的数据集：
FOR ds1_row IN ds1 LOOP FOR ds2_row IN ds2 LOOP output ds1_row and ds2_row END LOOP END LOOP
呼，结束了😩。这篇文章前前后后写了20多天，没想到比前几篇还要“难产”，因为不仅要把原理介绍清楚，也需要把每个案例也要清晰介绍，有时候自己能明白，但把知识通俗易懂的讲出来确实不容易，所以如果还有疑问的小伙伴可以私信我或者参考官网文档学习哦~🔍。
原文地址：https://blog.csdn.net/naisiing/article/details/142822857
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！