Oracle表连接方式总结 基金经理是否每天都要操作一次

目录

一、简介

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

三、NESTED LOOPS（嵌套循环）

四、HASH JOIN（哈希连接）

五、CARTESIAN PRODUCT（笛卡尔积）

六、参考资料

一、简介

Oracle中主要有下面四种表连接方式：

SORT MERGE JOIN（排序-合并连接）；NESTED LOOPS（嵌套循环）；HASH JOIN（哈希连接）；CARTESIAN PRODUCT（笛卡尔积）；

Oracle中，通过JOIN关键字进行表连接操作，一次只能连接两张表，JOIN 操作的各步骤一般是串行的（在读取做连接的两张表的数据时可以并行读取）。

需要说明两个重要的概念：驱动表与匹配表。注意这些概念只存在于NESTED LOOPS（嵌套循环）与 HASH JOIN（哈希连接）两种表连接方式中。

驱动表（Driving Table）：

表连接时首先存取的表，又称外层表（Outer Table），可以简单理解为查询的主表；

注意：如果驱动表返回的数据行数过多，必须会影响查询效率，所以我们一般选择小表(查询结果返回较少行数的表) 作为驱动表。

匹配表（Probed Table）：

表连接时后面存取的表，又称为内层表（Inner Table）,也叫被驱动表，可以简单理解为查询的副表；

从驱动表获取一行具体数据后，会到该表中寻找符合连接条件的行。故该表一般为大表（查询结果返回较多行数的表）。

下面通过一些示例对每一种表连接方式进行详解。

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

内部连接过程：

第一步：生成驱动表(主表)需要的数据，按照连接操作关联列对这些数据进行排序；第二步：生成匹配表(附表)需要的数据，按照与上面一步中对应的连接操作关联列对数据进行排序；第三步：两边已排序的行放在一起执行合并操作（对两边的数据集进行扫描并判断是否连接）；

示例：

explain plan for select * from zhxg_zhcp_cpjgb t1join zhxg_xsxx_xsjbxx t2on t1.xsid > t2.PKID;select * from table(dbms_xplan.display);

执行计划：

通过上述执行计划，可见查询使用到了排序-合并连接方式。

注意事项：排序-合并连接的表无驱动顺序，两个表是对等的，哪个表在前面都可以，可以考虑在操作关联列上建立索引让其能预先排好序，连接速度可大大提高。排序合并连接其实很耗费资源，因为要对2个表/结果集进行排序，所以一般情况下，CBO是不会选择走SORT MERGE JOIN的。

应用场景：当结果集已经排过序；

适用的连接条件有： =

不适用的连接条件有： 、like

三、NESTED LOOPS（嵌套循环）

内部连接过程：

a) 第一步：取出主表（驱动表）的第一行数据，遍历副表（匹配表）的所有行并检查是否有匹配的，取出匹配的行放入结果集中；b) 取出主表（驱动表）的第二行数据，遍历副表（匹配表）的所有行并检查是否有匹配的，取出匹配的行放入结果集中；c) 若主表（驱动表）中返回了 N 行数据，则副表（匹配表）也相应的会被全表遍历 N 次；

示例：

zhxg_zhcp_cpjgb数据表的记录数为100多条；

zhxg_xsxx_xsjbxx数据表的记录数为6000多条；

下面先以记录数较少的zhxg_zhcp_cpjgb作为驱动表进行测试，观察执行计划。

explain plan forselect /*+ leading(t1) use_nl(t2) */ * from zhxg_zhcp_cpjgb t1 join zhxg_xsxx_xsjbxx t2 on t1.xsid = t2.PKID;select * from table(dbms_xplan.display);

执行计划：

下面以记录数较多的zhxg_xsxx_xsjbxx作为驱动表进行测试，观察执行计划。

示例：

explain plan forselect /*+ leading(t2) use_nl(t1) */ * from zhxg_zhcp_cpjgb t1 join zhxg_xsxx_xsjbxx t2 on t1.xsid = t2.PKID;select * from table(dbms_xplan.display);

执行计划：

可见，这里以zhxg_xsxx_xsjbxx作为驱动表测试的话，可能是因为zhxg_xsxx_xsjbxx表pkid是主键，所以这里显示的并不是嵌套循环，而是下面我们即将介绍的哈希连接方式。

注意事项：

因为主表（驱动表）的每一行都会去匹配 副表（匹配表）的所有行，所以当主表（驱动表）返回的行数尽可能少并且能高效访问副表（匹配表）（如建立适当的索引）时，效率较高。应尽可能使用限制条件（Where过滤条件）使驱动表返回的行数尽可能少，同时在匹配表的连接操作关联列上建立唯一索引（UNIQUE INDEX）或是选择性较好的非唯一索引，此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多，即使匹配表连接操作关联列上存在索引，连接效率也不会很高。

小总结：

嵌套循环有驱动表和被驱动表的概念，驱动顺序不同执行计划差异非常大；驱动表只被访问一次，被驱动表被访问多次。嵌套循环访问表的次数直接受驱动表的返回记录数的影响。因此应当让实际返回记录数（A-Rows）小的表作为驱动表，返回记录数大的表作为被驱动表；在驱动表的查询条件上建立索引可以改善查询效率；在连接条件上建立索引也可以改善查询效率；在被驱动表查询条件建立索引对查询效率的影响要视情况而定，不一定带来好处； 四、HASH JOIN（哈希连接）

内部连接过程：

a) 取出主表（驱动表）的数据集，然后将其构建成内存中的一个 Hash Table（Hash函数的Hash KEY就是连接操作关联列），创建Hash位图（bitmap）；b) 取出副表（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置，在该位置上检查能否找到匹配的数据；

示例：

explain plan for select /*+ leading(t1) use_hash(t2)*/ * from zhxg_zhcp_cpjgb t1 join zhxg_xsxx_xsjbxx t2 on t1.xsid = t2.pkid; select * from table(dbms_xplan.display);

执行计划：

小总结：

驱动表和被驱动表都是最多只被访问一次；哈希连接的表有驱动顺序；哈希连接不适用于的连接条件是：不等于，大于>，小于