# 循环方法简介 ### nested loops join 嵌套循环连接是一种两个表做表连接时依靠两层嵌套循环得到连接结果集的表连接方法。 嵌套循环连接的优缺点及适用场景： - 如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列存在唯一性索引（或者在被驱动表的连接列存在选择性很好的非唯一性索引），此时嵌套循环连接的执行效率非常高；如果驱动表所对应的驱动结果集的记录数很多，即使在被驱动表的连接列存在索引，此时使用嵌套循环连接的执行效率也不会高。 - 大表也可以做驱动表，关键在于目标SQL的谓词条件能否将驱动结果集的数量降下来。 - 嵌套循环连接可以快速响应。排序合并连接要等到做完排序后合并操作时才能开始返回数据，哈希连接要等到驱动结果集所对应的hash table全部建完后才能开始返回数据。 ### sort merge join 排序合并连接是一种两个表在做表连接时用排序操作（sort）和合并操作（merge）来得到连接结果集的表连接方法。 排序合并连接的优缺点及适用场景： - 排序合并连接的执行效率不如哈希连接，但是排序合并连接的使用范围更广，因为哈希连接只用于等值连接，排序合并连接除等值连接还用于其他连接条件（<、<=、>、>=）。 - 排序合并连接不适合OLTP系统，对OLTP系统来说，排序是非常昂贵的操作。如果能避免排序操作，OLTP系统也可以使用排序合并连接。比如两个表虽然是排序合并连接，实际上并不需要排序，因为这两个表在各自的连接列都有索引。 - 排序合并连接不存在驱动表的概念。 ### hash join 在 Oracle10g 及以后的数据库版本中，优化器（实际上是 CBO，哈希连接仅适用于 CBO）在解析目标 SQL 时是否考虑哈希连接受限于隐含参数 \_HASH_JOIN_ENABLE，而在 Oracle10g 以前受限于隐含参数 HASH_JOIN_ENABLE。 USE_HASH Hint 的优先级比参数 \_HASH_JOIN_ENABLE 高，即使 \_HASH_JOIN_ENABLE 参数值为 FALSE，CBO 解析 SQL 时仍使用哈希连接。 哈希连接的优缺点及适用场景： - 哈希连接不一定会排序，大多数情况下不需要排序。 - 哈希连接的驱动表的连接列的可选择性应尽可能好，可选择性会影响 Hash Bucket 的记录数，Hash Bucket 的记录数会影响从该 Hash Bucket 中查找匹配记录的效率。 - 哈希连接只适用于 CBO 和等值连接。 - 哈希连接很适合于小表和大表之间做表连接而且连接结果集的记录较多的情况，特别是小表的连接列的可选择性非常好的情况，这时哈希连接的直线时间近似于全表扫描大表的耗费时间。 # 执行计划相关说明 - NESTED LOOPS 通过嵌套循环获取关联数据。 在进行嵌套循环关联时，第一个数据集是驱动数据集，即嵌套循环中的外循环 - NESTED LOOPS (ANTI) 通过嵌套循环获取关联数据。 在 NESTED LOOPS (ANTI) 的过程中，如果发现外循环中读取到的数据在内循环中能够匹配到，则立即终止内循环、丢弃该数据，开始下一轮循环。 - NESTED LOOPS (SEMI) 通过嵌套循环获取不完整关联数据。 在 NESTED LOOPS (SEMI) 的过程中，如果发现外循环中读取到的数据在内循环中能够匹配到，则立即终止内循环、返回该数据，开始下一轮循环。 - NESTED LOOPS (OUTER) 通过嵌套循环进行外关联，获取关联数据 我们通常说的关联，如果没有特别指明，都是说内关联。而外关联与内关联的不同之处在于，无论在内循环中是否找到匹配数据，外循环中的数据都会被返回。 - NESTED LOOPS (PARTITION OUTER) 以左外关联的左边数据集（或外关联的右边数据集）为外循环，将左外关联的右边数据集（或右外关联的左边数据集）分组（分区）进行外关联匹配 > 提示：对于分区左（右）外关联，从逻辑上看，左（右）表需要与右（左）表中的数据分组（分区）分别做外关联。如果实际操作也按照这个逻辑实现，则意味着每次与一组数据进行关联，都要读取一次左（右）表数据。而在 NESTED LOOPS (PARTITION OUTER) 中，第一次读取左（右）表数据后，就被缓存在私有内存中，从而避免了多次重复读取共享内存数据。 - HASH JOIN 通过数据集的哈希值匹配获取关联数据。 进行哈希关联时，先用哈希函数对左数据集（也称为构建数据集）按哈希值分区，并且创建哈希表，映射哈希键值与分区；然后逐条扫描右数据集（也称为勘测数据集）的数据记录，用相同的哈希函数获取哈希值，并与哈希表进行匹配，找到相应的构建数据集的哈希分区，然后再用分区中的数据进行精确匹配。 哈希值与原始数据之间的关系是一对多的关系。即对于同一个哈希算法，一条原始数据只会有一个哈希值，且多条不同数据的哈希值可能相同。 - HASH JOIN (ANTI) 通过数据集的哈希值匹配获取非关联数据。 在匹配的过程中，如果哈希值相同，且数值匹配，则立即终止对关联哈希表中剩余哈希值的匹配、丢弃该哈希值，开始下一轮匹配。 > 提示：如果作为驱动的数据集的哈希表非常大，以至于内存中哈希空间无法一次性完成两边哈希表的匹配，则会将驱动哈希表分为一个小的哈希表，一次匹配一个，其他的则暂时存储到临时表空间中。我们可以注意到上述执行计划中有对临时表空间的估算值 - HASH JOIN (RIGHT ANTI) 取右边数据集做驱动，通过数据集的哈希值匹配获取非关联数据。 如果驱动数据集太大，以至于需要分配临时空间暂存哈希表，优化器则会考虑采用数据量较少的关联数据集作为驱动。此时，就需要用 HASH JOIN (RIGHT ANTI) 进行关联；同样，在匹配的过程中，如果两边的哈希值相同且数值匹配，则立即终止对关联哈希表中剩余哈希值的匹配、丢弃条数，开始下一轮匹配；不同的是，如果所有记录都未匹配，则返回关联数据集中的数据，而非驱动数据集的数据。 - HASH JOIN (ANTI SNA) 通过数据集的哈希值匹配获取非关联数据，并且同时关注是否有空值。这一操作在 11g 中引入。非关联查询时，是否进行检测控制，可以由优化器参数 “_optimizer_null_aware_antijoin” 控制 - HASH JOIN (RIGHT ANTI SNA) 取右边数据集做驱动，通过数据集的哈希值匹配获取非关联数据，同时关注是否有空值。这一操作在 11g 中引入。 - HASH JOIN (SEMI) 通过数据集的哈希值匹配获取不完整关联数据。 在匹配过程中，如果哈希值相同、且数值匹配，则立即终止对关联哈希表中剩余哈希值的匹配、返回该哈希值，开始下一轮匹配。 - HASH JOIN (RIGHT SEMI) 取右边数据集做驱动，通过数据集的哈希值匹配获取不完整关联数据。 如果驱动数据集太大，为了避免读写临时表空间，优化器会考虑通过 HASH JOIN (RIGHT SEMI) 进行关联；同样，在匹配过程中，如果哈希值相同且数值匹配，则立即终止对关联哈希表中剩余哈希值的匹配、返回关联数据集中的哈希值，开始下一轮匹配；返回关联数据集中的数据，而非驱动数据集的数据。 - HASH JOIN (OUTER) 通过数据集的哈希值匹配进行外（左）关联数据关联。 这里的外关联，实际上就是左外关联。通过哈希值匹配进行外关联操作时，左数据集的数据无论是否匹配到右数据集，都会被获取。 - HASH JOIN (RIGHT OUTER) 通过数据集的哈希值匹配进行右外关联数据关联。 - HASH JOIN (FULL OUTER) 通过数据集的哈希值匹配进行关联数据完全关联 - MERGE JOIN 通过合并已排序的数据进行关联。 进行合并关联时，分别从两个数据集的首位开始对数据进行比较，如果相等，则说明数据匹配，则对两边数据集的下一条数据进行比较；如果不相等，则获取较小数值（如果为降序，则是大数值）所在数据集的下一条记录；当两边数据集都存在多条相等的记录时，需要在这多条记录之间进行多重影匹配。 - SORT (JOIN) 对数据进行排序，以执行合并关联操作。 排序后的数据缓存在私有内存中，因而可以减少对共享缓存的访问次数和锁阀的争用。 > 提示：在做合并关联时，要求两边数据集已经排好序 - MERGE JOIN (ANTI) 通过进行数据合并关联获取非关联数据。 通过合并关联获取非关联数据时，如果左指针指向的数据大于右边数据，则有指针向后移一位，进行下一次比较；如果相等，两边指针都向后移一位；如果左边数据小于右边数据，则返回左数据（因为右边不会存在与其相等的数据了），并且指针向后移动。 由于右数据集只是起到数据比较的作用，而不需要返回数据，因此会对右数据集进行唯一性排序，排除重复数据，以减少匹配次数。 - MERGE JOIN (SEMI) 通过进行数据合并关联获取不完整关联数据。 > MERGE JOIN (SEMI) 与 MERGE JOIN 不同之处在于，由于右数据集不需要返回数据，且与左数据集的数据记录相等的数据只需要匹配一次，因此右数据集，会进行唯一性排序，以排除重复数据。 - MERGE JOIN (OUTER) 通过进行数据合并关联而进行（左）外关联，获取关联数据。 通过合并关联进行左外关联时，当右边数据记录大于、等于左边数据记录时，都属于匹配记录。 - MERGE JOIN (PARTITION OUTER) 将右边数据集分组（分区），左数据集分别与每组数据进行合并外关联，以获取关联数据。 - MERGE JOIN (CARTESIAN) 通过合并关联对数据集进行笛卡尔关联。 进行笛卡尔关联时，两边数据集中的任何一条数据记录都会与关联数据集中的任何一条记录匹配，换句话说，它们之间不存在关联条件，两边数据集的任何两条记录都满足笛卡尔的关联匹配要求。 - JOIN FILTER (CREATE) 布隆过滤器：适用于判断一个元素是否属于一个数据集的数据结构。其基本思想就是用一个或多个哈希函数对数据集中的每个成员做映射，映射结果不是存在完整的哈希表中，而是一个向量中。位向量所有位初始都为 0，根据哈希结果将位向量中的相应位置 1.对数据集中的所有成员的哈希计算完成后，就得到了该数据集的位向量。当需要判断一个元素是否属于该数据集时，也用相同的哈希函数对其映射得到它的位向量，然后将其位向量上所有为 1 的位与数据集位向量上相应位比较，如果发现数据集的位向量上某个位为 0，可以判断这个元素不属于该数据集。而如果所有相应位都为 1 的话，那么该元素可能属于这个数据集。 创建一个过滤器，以用于布隆过滤器 - JOIN FILTER (USE) 使用系统创建的过滤器进行布隆过滤。 - PART JOIN FILTER (CREATE) 在哈希关联时创建布隆过滤器，用于分区裁剪 - PARTITION HASH (JOIN-FILTER) 利用布隆过滤，进行分区裁剪 # HINT 根据各类连表查询的特性，可以使用 hint 来调整连表方式，从而起到优化的效果 ### nested loops join - LEADING Hint （指定 Nested Loop Join 顺序） - USE_NL (指定使用 Nested Loop Join) **示例** ```sql select /*+ leading(b a) USE_NL(a) */ * from t1 a, t2 b where a.c1=b.c1; select /*+ leading(a b c) USE_NL(b c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1; ``` ### hash join - LEADING Hint （指定 Hash Join 顺序） - SWAP_JOIN_INPUTS Hint（指定 Build 表） - NO_SWAP_JOIN_INPUTS Hint（指定 Probe 表） - USE_HASH (指定使用 Hash Join) **示例** ```sql select /*+ leading(a b c) use_hash(b c) swap_join_inputs(b) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1; select /*+ leading(a b c) swap_join_inputs(b) swap_join_inputs(c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1; select /*+ leading(a c b) USE_HASH(c b) swap_join_inputs(c) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1; select /*+ leading(c b a) USE_HASH(c b) */ * from t1 a, t2 b, t3 c where a.c1=b.c1 and a.c1=c.c1; ``` ### sort merge join - use_merge：在 Hint 的作用下，CBO 生成的执行计划中使用 Merge Sort Join 连接方式。 **示例** ```sql select /*+use_merge(a,b)*/* from t1 a, t2 b where a.c1=b.c1; ``` # 优化 1. 关联方式的影响 - 使用等值连接，对于大数据量连表查询，可以避免 nested loop，而使用 hash join - 只有在等连接不可用的情况下事由区间连接 - 避免使用 != 或者 not - 避免使用 LIKE 匹配 2. 表检索集的影响 - 尝试查找特殊的列且列在小的数目中 - 过滤大表减少连接的行数，从过滤多的表向下查找，尽可能的过滤大表 - 除非很小的表，无论什么情况多有索引 - 使用分区将大表分区 3. 其它 - 检索顺序的影响 ORACLE 在解析 SQL 语句的时候对 FROM 子句后面的表名是从右往左解析的，在对多表查询中，一定要把小表写在最右边 - 检索方式的影响 可以使用 hint 来调整连接方式，大表关联多用 hash join，有小表多用 nested loop，排序聚合多用 merge/sort join # 参考 - 崔华：《基于Oracle的SQL优化》 - [SQL 执行计划的固定手段 --- Hint 方式（Hash Join）](https://blog.csdn.net/weixin_50513167/article/details/115612034) - [SQL 执行计划的固定手段 --- Hint 方式（Nested Loop Join）](https://blog.csdn.net/weixin_50513167/article/details/115836410)