use_nl,use_hash,use_merge的3种连接方式驱动与被驱动关系

最新推荐文章于 2021-04-07 20:53:06 发布
最新推荐文章于 2021-04-07 20:53:06 发布
阅读量5.1k 收藏 5
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/launch_225/article/details/27859219
版权

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

--use_nl,use_hash,use_merge的3种连接方式驱动与被驱动关系

一.HASH连接方式

--建一个小表和一个大一些的表
SQL> create table t1 as select * from dba_objects where rownum<11;

Table created.

SQL> create table t2 as select * from dba_objects;

Table created.

SQL> select count(1) from t1;

  COUNT(1)
----------
        10

SQL> select count(1) from t2;

  COUNT(1)
----------
     75211
    
--试验HASH，可以看出hash是存在驱动与被驱动关系，驱动表适合于两表间较小的一个，耗用更少的排序开销；     
SQL> select /*+ leading(t2) use_hash(t2,t1) */t1.status,t2.status
  2  from t1,t2
  3  where t1.object_id=t2.object_id;

STATUS  STATUS
------- -------
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID

10 rows selected.

--看到排序区中排序的开销为3439K；
SQL> @allstat

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  315bd4pgdyt86, child number 1
-------------------------------------
select /*+ leading(t2) use_hash(t2,t1) */t1.status,t2.status from t1,t2
where t1.object_id=t2.object_id

Plan hash value: 2959412835

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.08 |    1078 |   1071 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     10 |     10 |00:00:00.08 |    1078 |   1071 |  3439K|  1573K| 5617K (0)|
|   2 |   TABLE ACCESS FULL| T2   |      1 |  79628 |  75211 |00:00:00.02 |    1074 |   1071 |       |       |          |
|   3 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       4 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


25 rows selected.

--而T1表为驱动表时，排序的开销为1206K比上面的3439K小得多；
SQL> select /*+ leading(t1) use_hash(t2,t1) */t1.status,t2.status
  2  from t1,t2
  3  where t1.object_id=t2.object_id;

STATUS  STATUS
------- -------
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID
VALID   VALID

10 rows selected.

SQL> @allstat

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  3zc11bg9gdq31, child number 1
-------------------------------------
select /*+ leading(t1) use_hash(t2,t1) */t1.status,t2.status from t1,t2
where t1.object_id=t2.object_id

Plan hash value: 1838229974

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.08 |    1078 |   1071 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     10 |     10 |00:00:00.08 |    1078 |   1071 |  1206K|  1206K| 1126K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       3 |      0 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      1 |  79628 |  75211 |00:00:00.03 |    1075 |   1071 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


25 rows selected.

--而ORACLE在预估COST时第一类的COST也是大于第2类，也就是默认O是会采用第2种方式；
--第1类的COST为419，而第2类的COST预估为305；
SQL> set auto traceonly
SP2-0158: unknown SET autocommit option "traceonly"
Usage: SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n }
SQL> set autot traceonly exp
SQL> 
SQL> select /*+ leading(t2) use_hash(t2,t1) */t1.status,t2.status
  2  from t1,t2
  3  where t1.object_id=t2.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   360 |       |   419   (1)| 00:00:06 |
|*  1 |  HASH JOIN         |      |    10 |   360 |  2336K|   419   (1)| 00:00:06 |
|   2 |   TABLE ACCESS FULL| T2   | 79628 |  1399K|       |   301   (1)| 00:00:04 |
|   3 |   TABLE ACCESS FULL| T1   |    10 |   180 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> select /*+ leading(t1) use_hash(t2,t1) */t1.status,t2.status
  2  from t1,t2
  3  where t1.object_id=t2.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   360 |   305   (1)| 00:00:04 |
|*  1 |  HASH JOIN         |      |    10 |   360 |   305   (1)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |   180 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 79628 |  1399K|   301   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)
   
--HASH连接方式默认当然是采用后一种COST较小那类；
SQL> select t1.status,t2.status
  2  from t1,t2
  3  where t1.object_id=t2.object_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |   360 |   305   (1)| 00:00:04 |
|*  1 |  HASH JOIN         |      |    10 |   360 |   305   (1)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| T1   |    10 |   180 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 79628 |  1399K|   301   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

   
二.排序合并连接方式
--可以看到无论是哪种表排在前面先执行，其开销都是一样的；也就是说明merge方式是不存在驱动与被驱动的区别关系 ；
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2yw072zjb9h5b, child number 1
-------------------------------------
select /*+ leading(t1) use_merge(t2,t1) */t1.status,t2.status from
t1,t2 where t1.object_id=t2.object_id

Plan hash value: 412793182

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |00:00:00.06 |    1077 |   1071 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |     10 |     10 |00:00:00.06 |    1077 |   1071 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |     10 |     10 |00:00:00.01 |       3 |      0 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       3 |      0 |       |       |          |
|*  4 |   SORT JOIN         |      |     10 |  79628 |     10 |00:00:00.06 |    1074 |   1071 |  2675K|   740K| 2377K (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |  79628 |  75211 |00:00:00.02 |    1074 |   1071 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


28 rows selected.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  csjsgn0hh4dr6, child number 1
-------------------------------------
select /*+ leading(t2) use_merge(t2,t1) */t1.status,t2.status from
t1,t2 where t1.object_id=t2.object_id

Plan hash value: 1792967693

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     10 |00:00:00.09 |    1077 |   1071 |       |       |          |
|   1 |  MERGE JOIN         |      |      1 |     10 |     10 |00:00:00.09 |    1077 |   1071 |       |       |          |
|   2 |   SORT JOIN         |      |      1 |  79628 |     54 |00:00:00.09 |    1074 |   1071 |  2675K|   740K| 2377K (0)|
|   3 |    TABLE ACCESS FULL| T2   |      1 |  79628 |  75211 |00:00:00.02 |    1074 |   1071 |       |       |          |
|*  4 |   SORT JOIN         |      |     54 |     10 |     10 |00:00:00.01 |       3 |      0 |  2048 |  2048 | 2048  (0)|
|   5 |    TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       3 |      0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
       filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

三.嵌套循环连接方式是影响最为明显的，buffers数相差了10倍之多；

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  35g7vtpc63s04, child number 0
-------------------------------------
select /*+ leading(t1) use_nl(t2,t1) */t1.status,t2.status from t1,t2
where t1.object_id=t2.object_id

Plan hash value: 1967407726

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.11 |   10745 |  10710 |
|   1 |  NESTED LOOPS      |      |      1 |     10 |     10 |00:00:00.11 |   10745 |  10710 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |     10 |     10 |00:00:00.01 |       4 |      0 |
|*  3 |   TABLE ACCESS FULL| T2   |     10 |      1 |     10 |00:00:00.11 |   10741 |  10710 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)
   
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1a5dw45kqph9p, child number 0
-------------------------------------
select /*+ leading(t2) use_nl(t2,t1) */t1.status,t2.status from t1,t2
where t1.object_id=t2.object_id

Plan hash value: 4016936828

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     10 |00:00:00.93 |     226K|   1071 |
|   1 |  NESTED LOOPS      |      |      1 |     10 |     10 |00:00:00.93 |     226K|   1071 |
|   2 |   TABLE ACCESS FULL| T2   |      1 |  79628 |  75211 |00:00:00.06 |    1075 |   1071 |
|*  3 |   TABLE ACCESS FULL| T1   |  75211 |      1 |     10 |00:00:00.74 |     225K|      0 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


25 rows selected.

        
小结：从以上可看出，影响大到小的顺序为nl->hash->merge(不影响); 
应用：日常工作环境中经常可看到，由于nl方式不当而应使用hash的案例，毕竟NL对数据量的大小影响是最为敏感；
而ORACLE选择NL方式并没有错，错就错在预估值那里，所以当用explain plan for....,autot等方式看并不能看出问题；
当通过running time的统计信息收集然后select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
就会发现真正的原因是预估值与实际值的问题相差过大导致的；
在这种情况下执行计划的绑定是比较实用和有效的；


launch_225
关注
Oracle Hint:USE_NL、USE_MERGE、USE_HASH
junmail的专栏
12-28 7806
Oracle Hint:USE_NL、USE_MERGE、USE_HASH备查
oracle distinct 优化 no_merge,DM7 达梦数据库 查询优化 -- HINT 提示
weixin_36195837的博客
04-05 578
在上节看了DM 执行计划的信息：https://www.cndba.cn/dave/article/3633这里看下hint的相关用法。1 Hint 概述DM 查询优化器采用基于代价的方法。在估计代价时，主要以统计信息或者普遍的数据分布为依据。在大多数情况下，估计的代价都是准确的。但在一些比较特殊的场合，例如缺少统计信息，或统计信息陈旧，或抽样数据不能很好地反映数据分布时，优化器选择的执行计划不是...
Oracle学习笔记（三）——Hash连接
biww620的博客
06-27 901
哈希链接 在上一篇学习笔记二中对Nested Loop Join进行了讨论，通过多次测试得出了嵌套循环链接具有以下特点： 嵌套循环有驱动表和被驱动表的概念，驱动顺序不同执行计划差异非常大驱动表只被访问一次，被驱动表被访问多次。嵌套循环访问表的次数直接受驱动表的返回记录数的影响。因此应当让实际返回记录数（A-Rows）小的表作为驱动表，返回记录数大的表作为被驱动表。嵌套循环链接是没有排序
oracle usehash,关于use_nl和use_hash的联合使用
weixin_39608394的博客
04-07 440
我在10g下试的，好像和表的出现顺序有关啊，from a,c,b和fromb,c,a执行计划不一样SQL> SELECT /*+ use_hash(a,c) use_nl(c,b) */2 *3FROM a, c, b4WHERE1 = 15AND a.id = c.id6AND b.id = c.id;Execution Plan----------...
USE_HASH
热门推荐
落落的专栏 专注SQL调优 性能调优
12-01 3万+
use_hash(table1,table2) 指定table1,table2连接以HASH 方式连接，采用这种方式，我们不能控制table1,table2哪个表为驱动表，优化器会根据cost自动选择驱动表，如果我们非要控制table1为驱动表，可以加上leading(table1)作为提示，或者用ordered 作为提示。另外如果我们使用use_hash(table1)有可能优化器不会选
USE_HASH、USE_NL与驱动表
cnqc11810的博客
07-01 709
use_hash(table1,table2) 指定table1,table2连接以HASH 方式连接，采用这种方式，我们不能控制table1,table2哪个表为驱动表， 优化器会根据cost自动选择驱动表，如果我们非...
Oracle数据库的三种表连接技术：NESTEDLOOP, HASHJOIN, SORTMERGEJOIN
"Oracle数据库中的表连接方式包括NESTED LOOP、HASH JOIN和SORT MERGE JOIN，这三种方法各有特点，适用于不同的数据处理场景。在实际操作中，选择合适的连接方式对查询性能有着显著影响。" Oracle数据库在处理表...
Oracle 表三种连接方式使用介绍(sql优化)
09-10
Oracle数据库在处理表之间的连接操作时提供了三种主要的连接方式：Nested Loop Join、Hash Join以及Sort Merge Join。这些连接方式各有特点，适用于不同的情景，理解它们的工作原理和适用条件对于SQL查询优化至关...
Oracle SQL优化：强制NL连接驱动表的提示技巧
3. `/*+ leading(t2) use_nl(t) */` - 使用`leading`提示，可以明确指定`t2`作为NL连接的驱动表，`t`为被驱动表。 Oracle的内存结构包括SGA（System Global Area）和PGA（Program Global Area）。SGA由多个组件组成...
oracle哈希连接举例,Oracle表联接之哈希连接
weixin_33050129的博客
04-06 1110
Oracle表连接之哈希连接Oracle Hash join 是一种非常高效的join 算法，主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接，我们将小表构建到内存中，称为Hash cluster，大表称为probe表。当两个表做hash join时，oracle会选择一个表作为驱动表，先根据过滤条件排除不必要的...
hints ordered leading use_nl
u012461550的专栏
01-11 694
1:ordered  就是表示根据 from 后面表的顺序join，从左到右，左边的表做驱动表。连接方式是 HASH JOIN2：use_nl（）use_nl（t1）如果只带了一个表名作为参数，则该表为被驱动表，连接方式是    HASH JOINuse_nl（t1,t2）表示对表t1、t2关联时采用嵌套循环连接     NESTED LOOPS但是并不能让优化器确定谁是驱动表或谁是被驱动的表3:
[转]oracle 使用leading, use_nl, rownum调优例子
空心居
02-29 1357
1、使用leading和use_nl来设置表的查询顺序，来加快查询速度，一般把小表设为第一个表。/*+LEADING(TABLE)*/　　将指定的表作为连接次序中的首表./*+USE_NL(TABLE)*/　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.成本计算方法：设小表100行，大表100000行。两表均有索引：如果小表在内，大表在外(驱动表)的话，则扫描次数为：10
关于USE_NL的使用
cizongzhic79075的博客
07-19 298
关于USE_NL的使用 之前一直都不是很清楚这个怎么在Nest Loop中指定驱动表(外部表) 和 内部表，经过了一些实验对这一部分有了一定的认识: 首先对于nest loop有两种机制: 内部表全表...
oracle 使用leading, use_nl, rownum调优
巫山云的专栏
08-26 7488
1、使用leading和use_nl来设置表的查询顺序，来加快查询速度，一般把小表设为第一个表。 /*+LEADING(TABLE)*/ 　　将指定的表作为连接次序中的首表. /*+USE_NL(TABLE)*/ 　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表. 成本计算方法： 设小表100行，大表100000行。 两表均有索引： 如果小表在内，大表在外(驱动表)
merge into user用法、oracle实例
u013967958的博客
12-17 287
user表有id,name,age三个字段。执行下面的sql: merge into user    using(select '12' as id,          '小丸子' as name,            '20' as age            from dual) t//--别名 on user.id= t.id   when mathed then//--情
oracle hint use_nl,了解如何利用ordered,use_nl(),leading(),index()进行调优 ...
weixin_35828255的博客
04-03 1434
首先我们来分别介绍一下这四个hint的概念：1. ORDERED hint:ordered hint 指示oracle按照from关键字后的表顺序来进行连接。如果没有及时收集统计信息，查询优化器没有得到足够信息，此时你可以自行选择适当的inner及outer表进行连接。ORACLE推荐使用LEADING hint,它比ordered hint有更多的用途。2. LEADING hint:leadi...
oracle hash leading,oracle 使用leading, use_nl, rownum调优(引用)
weixin_30218733的博客
04-04 187
成本计算方法：设小表100行，大表100000行。两表均有索引：如果小表在内，大表在外(驱动表)的话，则扫描次数为：100000+100000*2 (其中2表示IO次数，一次索引，一次数据)如果大表在内，小表在外(驱动表)的话，则扫描次数为：100+100*2.两表均无索引：如果小表在内，大表在外的话，则扫描次数为：100000+100*100000如果大表在内，小表在外的话，则扫描次数为：100...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值