oracle 表连接方式： nested loop 嵌套循环和Hash Join的比较

最新推荐文章于 2021-04-08 08:04:07 发布
最新推荐文章于 2021-04-08 08:04:07 发布
阅读量1.7w 收藏 57
点赞数 12
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/daf380/article/details/82964378
版权

一、创建两张实验用表：wireless_site.merchant和wireless_site.bb

SQL> select count(*) from wireless_site.merchant;

  COUNT(*)
----------
     14005

SQL>

SQL> select count(*) from wireless_site.clickthroughrate;

  COUNT(*)
----------
   2384026

SQL> create table wireless_site.bb as select * from wireless_site.clickthroughrate;

Table created.

SQL> 
SQL> select count(*) from wireless_site.bb;

  COUNT(*)
----------
   2384026

SQL>

二、执行SQL语句并查看sql语句的真实执行计划：

SQL> alter session set statistics_level=all;

Session altered.

SQL> 

2.1、首先查看Hash Join的执行计划：

执行SQL语句：select  * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';

等待SQL语句执行完毕，然后再使用这条SQL语句：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执行计划：

由以上执行计划可以看出：

1、HASH JOIN的驱动表为BB，扫描方式为全表扫描，执行了1次，实际返回了1658行

2、HASH JOIN的被驱动表为MERCHANT，扫描方式为全表扫描，执行了1次，实际返回了12行

3、需要注意的是：HASH JOIN的驱动表被被驱动表都会被扫描一次，而nested loop则是驱动表扫描一次，被驱动表被扫描N次（具体N的值是根据驱动表返回的行数来决定的。）

 

2.2、接着使用HINT的方式让优化器强制走nested loop（使用wireless_site.merchant作为驱动表）

执行SQL语句：select /*+ leading(merchant) use_nl(bb) */ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';

等待SQL语句执行完毕，然后再使用这条SQL语句：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执行计划：

此时，wireless_site.merchant作为驱动表，加上过滤条件filter后，实际返回行数为：

由以上执行计划可以看出：

1、嵌套循环的驱动表为merchant，采用的扫描方式为全表扫描，执行了1次，返回了12行

2、嵌套循环的被驱动表为bb，采用的扫描方式是全表扫描，执行了12次，实际返回了1658行

 

 

2.3、然后再使用HINT的方式让优化器强制走nested loop（使用wireless_site.bb作为驱动表）

执行SQL语句：select /*+ leading(bb) use_nl(merchant) */ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';
等待SQL语句执行完毕，然后再使用这条SQL语句：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执行计划：

此时，wireless_site.bb作为驱动表，加上过滤条件filter后，实际返回行数为：

由以上执行计划可以看出：

1、表BB作为驱动表，扫描方式是全表扫描，实际返回行数为1658

2、表merchant作为被驱动表，采用的扫描方式为索引唯一扫描，执行了1658次，返回了1658行

3、被驱动表merchant索引扫描完成后，通过rowid回表读数执行了1658次，实际返回了1658行

 

 

2.4、接下来在wireless_site.bb表中的recordtype列上创建索引

SQL> create index wireless_site.bb_idx on wireless_site.bb(recordtype);

Index created.

SQL>

接着使用HINT的方式让优化器强制走nested loop（使用wireless_site.merchant作为驱动表）:

执行SQL语句：select /*+ leading(merchant) use_nl(bb) */ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';

等待SQL语句执行完毕，然后再使用这条SQL语句：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执行计划：

由执行计划可以看出：

1、merchant作为驱动表，采用的是全表扫描的方式，实际返回行数为12

2、被驱动表BB的索引采用的是Index Range Scan的方式，实际返回行数为1658，共执行了12次

3、被驱动表BB索引被访问了12次，每次扫描完索引后又根据rowid回表读数（TABLE ACCESS BY INDEX ROWID），这样BB表就被访问了1658次

对比之前没有在wireless_site.bb表中的recordtype列上创建索引时的执行计划可以看出，创建索引后SQL花费不到1s的时间就可以出结果；没有创建索引时，需要花费1分32秒左右。

 

2.5、创建索引后，使用HINT的方式让优化器强制走nested loop（使用wireless_site.bb作为驱动表）:

执行SQL语句：select /*+ leading(bb) use_nl(merchant) */ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';
等待SQL语句执行完毕，然后再使用这条SQL语句：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执行计划：

由执行计划可以看出：

1、嵌套循环的驱动表为bb，扫描方式为全表扫描，执行了1次，实际返回了1658行

2、嵌套循环的被驱动表的索引为SYS_C0011475，扫描方式为INDEX  UNIQUE SCAN，执行了1658次，实际返回了1658行

3、嵌套循环的被驱动表为merchant，merchant的所用被扫描了1658次，每次扫描完索引后又根据rowid回表（TABLE ACCESS BY INDEX ROWID），执行了1658次，实际返回了1658行

4、sql语句执行完，大概需要7.42秒

 

2.6、创建索引后，使用HINT强制走HASH JOIN（wireless_site.bb作为驱动表）

执行SQL语句：select /*+ leading(bb) use_hash(merchant)*/ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';
等待SQL语句执行完毕，然后再使用这条SQL语句：select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));查看真实执行计划：

由执行计划可以看出：

1、HASH JOIN的驱动表为BB，扫描方式为全表扫描，执行了1次，实际返回了1658行

2、HASH JOIN的被驱动表为merchant，扫描方式为全表扫描，执行了1次，实际返回了12行

3、使用HASH Join 执行时，花费时间约为8.54秒

 

三、结论：

3.1、在nested loop 中，驱动表返回一行数据，被驱动表要被扫描一次

3.2、在nested loop中，往往是将小表作为驱动表，大表作为被驱动表。所谓小表，不是说这个表的segment_size的大小，而是指返回数据的多少；返回数据量少，我们就称之为小表，返回数据量大，我们称之为大表。

3.3、在nested loop中，如果发现SQL的执行计划中的nested loop使用了全表扫描，这时我们就要特别注意了：直接select count(*) from 驱动表where 过滤条件...看它返回多少行（检查驱动表返回的总行数），看他是否能不走全表扫描。

3.4、HASH JOIN的驱动表和被驱动表都被扫描1次。

3.4、HASH JOIN会耗费PGA的work_area。嵌套循环不耗费PGA的work_area。

 

 

四、附录

4.1、在SQL语句优化过程中，我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法：

1. /*+ALL_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.

例如:SELECT /*+ALL+_ROWS*/ merchantid,merchantname,status FROM merchant WHERE merchantid='3712098761';

2. /*+FIRST_ROWS*/表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.

例如:SELECT /*+FIRST_ROWS*/ merchantid,merchantname,status FROM merchant WHERE merchantid='3712098761';

3. /*+CHOOSE*/表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;

例如:SELECT /*+CHOOSE*/ merchantid,merchantname,status FROM merchant WHERE merchantid='3712098761';

4. /*+RULE*/表明对语句块选择基于规则的优化方法.

例如:SELECT /*+ RULE */ merchantid,merchantname,status FROM merchant WHERE merchantid='3712098761';

5. /*+FULL(TABLE)*/表明对表选择全局扫描的方法.

例如:SELECT /*+FULL(A)*/ merchantid,merchantname,status FROM merchant WHERE merchantid='3712098761';

6. /*+ROWID(TABLE)*/提示明确表明对指定表根据ROWID进行访问.

例如:SELECT /*+ROWID(BSEMPMS)*/ * FROM merchant WHERE ROWID>='AAAAAAAAAAAAAA'AND merchantid='3712098761';

7. /*+CLUSTER(TABLE)*/提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.

例如:SELECT /*+CLUSTER */ merchant.merchantid,package.packagename FROM merchant,package where merchantid='3712098761' AND merchant.merchantid=package.merchantid;

8. /*+INDEX(TABLE INDEX_NAME)*/表明对表选择索引的扫描方法.

例如:SELECT /*+INDEX(bb bb_idx)  */ FROM wireless_site.bb WHERE recordtype='3712098761';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/表明对表选择索引升序的扫描方法.

例如:SELECT /*+INDEX_ASC(bb bb_idx) */ FROM wireless_site.bb WHERE recordtype='3712098761';

10. /*+USE_HASH(TABLE)*/将指定的表与其他行源通过哈希连接方式连接起来.

例如:SELECT /*+USE_HASH(bb,merchant)*/ * FROM wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';

or：select /*+ leading(bb) use_hash(merchant)*/ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';

11. /*+USE_NL(TABLE)*/将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

例如:SELECT /*+bb USE_NL(merchant)*/ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';

or：select /*+ leading(merchant) use_nl(bb) */ * from wireless_site.merchant,wireless_site.bb where bb.recordtype = merchant.merchantid and merchant.merchantid like '%3210%';

Leading()： 指示Oracle在执行join(hash join, nested loop join, merge join)时的连接顺序。当执行计划不按照最优的表连接顺序时，考虑使用Leading 改变执行计划。 使用leading 的目的是减少检索数据量， 提高查询性能

 

4.2、查看某张表的统计信息是否过期

执行如下SQL语句查询：

SELECT OWNER,TABLE_NAME,OBJECT_TYPE,STALE_STATS,LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE (STALE_STATS='YES' OR LAST_ANALYZED IS NULL) AND OWNER NOT IN ('SYS','SYSTEM','SYSMAN','DMSYS','OLAPSYS','XDB','EXFSYS','CTXSYS','WMSYS','DBSNMP','ORDSYS','OUTLN','TSMSYS','MDSYS') AND TABLE_NAME NOT LIKE 'BIN%';

结果如下：

或者，这行这条SQL：

select owner,table_name,object_type,stale_stats,last_analyzed from dba_tab_statistics where table_name in (table_name) and owner = 'WIRELESS_SITE' and (stale_stats = 'YES' or last_analyzed in null);

或者执行这条SQL：

SELECT OWNER,TABLE_NAME,OBJECT_TYPE,STALE_STATS,LAST_ANALYZED FROM DBA_TAB_STATISTICS WHERE OWNER='&OWNER' AND TABLE_NAME='&TABLE_NAME';

 

首先创建一张实验表：

create table wireless_site.xxx as select rownum as col1, rownum + 1 as col2, rownum + 2 as col3 from dual connect by level <= 10000;

查看表结构

对该表收集统计信息：

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'wireless_site',
tabname => 'xxx',
estimate_percent => 30,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/

运行如下两个脚本：

exec dbms_stats.flush_database_monitoring_info; 

和

select owner,table_name,object_type,stale_stats,last_analyzed from dba_tab_statistics where table_name in (table_name) and owner = 'WIRELESS_SITE' and (stale_stats = 'YES' or last_analyzed in null);

结果显示没有记录是因为我的表是刚刚收集完统计信息，所以没有过期。

接下来删除表中20%的数据：

delete from wireless_site.xxx where rownum<=(select count(*) from wireless_site.xxx)*0.2;

然后再来运行脚本：exec dbms_stats.flush_database_monitoring_info;

查看结果，已过期：

或者：

或者：

 

daf380
关注
专栏目录
Oracle典型的表连接类型（sort merge join(SMJ) 排序-合并连接、nested loops(NL) 嵌套循环、hash join 哈希连接）
林世广
02-02 2334
1、sort merge join(SMJ) 排序-合并连接 连接过程： 将两个表排序，然后再进行join ①首先生产drving table【驱动表】需要的数据，然后对这些数据按照连接操作关联列进行排序； ②然后生产probed table【被探查表】需要的数据，然后对这些数据按照与driving table对应的连接操作列进行排序； ③最后两边已经排序的行被放在一起执行合并操作。 应用场景： ①排序是一个费时、费资源的操作，特别对于大表。所以SMJ通常不是一个特别有效的连接方法，但是如果driving
oracle嵌套三层循环语句,Oracle表连接操作——Nest Loop Join（嵌套循环）
weixin_30062621的博客
04-02 1475
关系型数据库并不是最早出现的数据库表现形式，之前还存在层次、网状数据库结构。随着关系型数据库的出现，以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接，借助各种类型的表连接，可以将平铺直叙的信息加以组装拼接。在Oracle数据库中，应对不同的连接情况和数据特征，数据库CBO会生成不同的执行计划和连接操作。本篇介绍各种连接中的一种——Nest Loop Join嵌...
Nested loops、Hash join、Sort merge join（三种连接类型原理、使用要点）
张冲andy的博客
04-28 2278
nested loop 嵌套循环（原理）： oracle从较小结果集（驱动表、也可以被称为outer）中读取一行，然后和较大结果集（被侦查表，也可以叫做inner）中的所有数据逐条进行比较（也是等值连接，也可以是非等值连接），如果符合规则，就放在结果集中，然后取驱动表的下一条数据继续循环，直到结束。 简单的理解就是：从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个
HASH JOIN ,MERGE JOIN ,NESTED LOOP
cufen6148的博客
04-09 179
NESTED LOOP:对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子...
Hash join 和nested loop
weixin_30652491的博客
10-25 161
Hash join 和nested loop 总所周知，Oracle数据库常用的两种优化器：RBO（rule-based-optimizer）和CBO(cost-based-optimizer)。目前更多地采用CBO(cost-based-optimizer)基于开销的优化器。在CBO方式下，Oracle会根据表及索引的状态信息来选择计划；在R...
Oracle的Filter,Nest loop,Merge sort join和Hash join(原创)
czmmiao的专栏
02-18 350
 Merge Sort Join按照Merge Sort Join连接的两表地位完全相同。这种算法会把每个表按照连接列进行排序，生成两个排序集。然后对两个排序集进行一次遍历便可以得到最终结果集。这个算法的特点是，每个表都需要排序，排序后都需要遍历一次。 以下面的例子说明，Merge Sort Join的执行过程如下：SQL&gt; explain plan for select /*+ us...
图解Oracle 表连接优化之嵌套循环连接（Nested loops join）
热门推荐
梅森上校的博客 业精于勤荒于嬉，形成于思毁于随。
06-22 1万+
当一条SQL语句引用多张表连接时，Oracle的查询优化器（Optimizer）不仅
mysql嵌套循环+半连接_SQL调优之四：嵌套循环连接(Nested Loops Joins)
weixin_39849479的博客
01-18 548
嵌套循环连接嵌套循环连接一个外部数据集到内部数据集中，针对外部数据集的每一行数据，数据库会去匹配内部数据集中所有匹配谓词条件的行。如果这个时候内部数据集或者内部表有索引可用，那么数据库就会通过使用它来定位rowid来获取数据。优化器什么时候考虑使用嵌套循环连接？一般来说，嵌套循环连接在小表之间，并且连接条件是带有索引的情况下是最适用的。如果一个数据集只有一行数据，比如说主键列上的等式查询(expl...
Oracle数据库的三种表连接技术：NESTEDLOOP, HASHJOIN, SORTMERGEJOIN
"Oracle数据库中的表连接方式包括NESTED LOOP、HASH JOIN和SORT MERGE JOIN，这三种方法各有特点，适用于不同的数据处理场景。在实际操作中，选择合适的连接方式对查询性能有着显著影响。" Oracle数据库在处理表...
多表连接的三种方式详解 HASH JOIN、MERGE JOIN、NESTED LOOP
微信搜：import_bigdata，大数据领域硬核原创作者
10-04 3906
点击上方蓝色字体，选择“设为星标”回复”资源“获取更多资源大数据技术与架构点击右侧关注，大数据开发领域最强公众号！大数据真好玩点击右侧关注，大数据真好玩！多表之间的连接有三种方式：Nes...
nested loops/hash join
iteye_4222的博客
12-24 253
nested loops适用于一大一小表。其中内表是小表，每取一次值，然后与外表匹配。 若内表复杂度为n，外表复杂度为m，则整体为O(n*m) hash join 适用于大表关联。若内表复杂度为n，外表复杂度为m，则整体为O(n+m) 索引： 特殊情况下，索引扫描反而不如全表扫描效率高。如索引字段值90%为同一值时，全表扫描效率更高。当过滤字段加上后，选取数据量较少时，索引能够大大发挥...
三种类型的表关联hash join和merge join和nested loop详解
rede
02-24 6632
https://blog.csdn.net/gao1440156051/article/details/52388644 多表之间的连接有三种方式： Nested Loops Hash Join Sort Merge Join 一． NESTED LOOP: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行...
NESTED LOOPS HASH JOIN
神谕梦醒
12-31 443
嵌套循环(NESTED LOOPS) Oracle从较小结果集(驱动表/外部表)中读取一行，然后和较大结果集(被探查表/内部表)中的所有数据逐条进行比较(嵌套循环可以用于非等值连接)，如果符合规则，就放入结果集中，然后取较小结果集的下一条数据继续进行循环，直到结束。嵌套循环只适合输出少量结果集或者是用于快速输出结果集。 哈希连接(HASH JOIN) 如果要返回大量结果集(比如返回200
oracle hash连接语法,oracle 多表连接的三种方式 HASH JOIN 、MERGE JOIN 、NESTED LOOP
weixin_39761422的博客
04-08 873
1、NESTED LOOP(嵌套循环连接)对于被连接的数据子集较小的情况。1)、 过程：从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个二重循环。2)、原理：for(int i=0;i<=5;i++) {for(int j=0;j<=10;j++) {System.out.println("伪代码");}}nested loop join的原理就是这个，只...
ORACLE 连接方式 NESTED LOOP、HASH JOIN
ado1986的专栏
03-07 1538
转载自：http://zl198751.iteye.com/blog/992795 ORACLE 连接方式 NESTED LOOP      对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是 nested loops。  一般在nested loop
oracle 主键关联 效率,Hash join与nested loops join的效率比较
weixin_35710880的博客
04-04 481
创建测试表SQL> create table t as select rownum id from dba_objects;Table created.SQL> create index idx_t on t(id);Index created.SQL> exec dbms_stats.gather_table_stats('sys','t',cascade=>true);...
Oracle数据库中的表连接方式及使用场合
ctpq29224的博客
04-16 246
表连接的方式及其使用场合: NESTED LOOP 对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没...
HASH JOIN ,MERGE JOIN ,NESTED LOOP详解
袈裟本无清净，红尘不染性空。幽幽古刹千年钟，都是痴人说梦
12-24 163
1，排序 - - 合并连接（Sort Merge Join， SMJ） 　　内部连接过程： 　　1） 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列（如A.col3）进行排序。 　　2） 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列（如B.col4）进行排序。 　　3） 最后两边已排序...
评论 4
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值