Oracle表连接方式总结

目录

一、简介

二、 SORT MERGE JOIN(排序-合并连接)

三、NESTED LOOPS(嵌套循环)

四、HASH JOIN(哈希连接)

五、CARTESIAN PRODUCT(笛卡尔积)

六、参考资料


一、简介

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

  1. SORT MERGE JOIN(排序-合并连接);
  2. NESTED LOOPS(嵌套循环);
  3. HASH JOIN(哈希连接);
  4. CARTESIAN PRODUCT(笛卡尔积);

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

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

  • 驱动表(Driving Table):

表连接时首先存取的表,又称外层表(Outer Table),可以简单理解为查询的主表;

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

  • 匹配表(Probed Table):

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

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

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

二、 SORT MERGE JOIN(排序-合并连接)

内部连接过程:

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

示例:

explain plan for select * from zhxg_zhcp_cpjgb t1
join zhxg_xsxx_xsjbxx t2
on 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 for
select /*+ 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 for
select /*+ 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)或是选择性较好的非唯一索引,此时嵌套循环连接的执行效率会变得很高。若驱动表返回的行数较多,即使匹配表连接操作关联列上存在索引,连接效率也不会很高。

小总结:

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

四、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);

执行计划:

小总结:

  1. 驱动表和被驱动表都是最多只被访问一次;
  2. 哈希连接的表有驱动顺序;
  3. 哈希连接不适用于的连接条件是:不等于<>,大于>,小于<,小于等于<=,大于等于>=,like;

五、CARTESIAN PRODUCT(笛卡尔积)

当两个数据表做连接,但是它们之间没有关联条件时,就会在两个row source中做笛卡儿乘积,这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积,如在星形连接中,除此之外,我们要尽量使用笛卡儿乘积,否则,查询效率会非常慢。

假如表t1有n行,t2表有m行,笛卡尔乘积的结果就是得到n * m行结果。

explain plan for select /*+ leading(t1) use_hash(t2)*/
 *
  from zhxg_zhcp_cpjgb t1,    --忘记写关联关系
   zhxg_xsxx_xsjbxx t2;
select *
  from table(dbms_xplan.display);

执行计划:

观察上面的执行计划,CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。想一下,如果两个表的数据量都非常大,那么查询效率可想而知。

六、参考资料

https://blog.csdn.net/waterxcfg304/article/details/25873265

https://blog.csdn.net/biww620/article/details/73477435/

https://www.cnblogs.com/Dreamer-1/p/6076440.html

https://blog.csdn.net/waterxcfg304/article/details/25872505

https://www.iteye.com/blog/keepwork-1949520

http://blog.itpub.net/28536251/viewspace-2140365/

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页
实付 19.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值