ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > use_nl,use_hash,use_merge的3种连接方式驱动与被驱动关系

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

Oracle 作者：hd_system 时间：2016-11-09 16:20:58 0 删除 编辑

哈希连接（HASH JOIN）是一种两个表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

对于排序合并连接，如果两个表在施加了目标SQL中指定的谓词条件后得到的结果集很大而且需要排序，则排序合并连接的执行效率一定不高；而对于嵌套循环连接，如果驱动表所对应的驱动结果集的记录数很大，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也会同样不高。为了解决这个问题，于是Oracle引进了哈希连接。在ORACLE 10g及其以后的版本中，优化器 （实际上是CBO，因为哈希连接仅适用于CBO）在解析目标SQL的时候是否考虑哈希连接受限于隐含参数_HASH_JOIN_ENABLED,默认值是TRUE.

对于哈希连接的优缺点及适用场景如下：

a,哈希连接不一定会排序，或者说大多数情况下都不需要排序

b,哈希连接的驱动表所对应的连接列的选择性尽可能好。

c,哈希只能用于CBO，而且只能用于等值连接的条件。（即使是哈希反连接，ORACLE实际上也是将其换成等值连接）。

c,哈希连接很适用小表和大表之间做连接且连接结果集的记录数较多的情形，特别是小表的选择性非常好的情况下，这个时候哈希连接的执行时间就可以近似看做和全表扫描个个大表的费用时间相当。

e,当两个哈希连接的时候，如果在施加了目标SQL中指定的谓词条件后得到的数据量较小的那个结果集所对应的HASH TABLE能够完全被容纳在内存中（PGA的工作区），此时的哈希连接的执行效率非常高。


oracle表之间的连接之哈希连接(Hash Join)，其特点如下：

1，驱动表和被驱动表都是最多只被访问一次。

2，哈希连接的表有驱动顺序。

3，哈希表连接的表无需要排序，但是他在做连接之前做哈希运算的时候，会用到HASH_AREA_SIZE来创建哈希表。

4，哈希连接不适用于的连接条件是：不等于<>，大于>，小于<，小于等于<=，大于等于>=，like。

5，哈希连接索引列在表连接中无特殊要求，与单表情况无异。

************************************************************************************************
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

点击(此处)折叠或打开

  1. --use_nl,use_hash,use_merge的3种连接方式驱动与被驱动关系
  2.       
  3.     一.HASH连接方式
  4.       
  5.     --建一个小表和一个大一些的表
  6.     SQL> create table t1 as select * from dba_objects where rownum<11;
  7.       
  8.     Table created.
  9.       
  10.     SQL> create table t2 as select * from dba_objects;
  11.       
  12.     Table created.
  13.       
  14.     SQL> select count(1) from t1;
  15.       
  16.       COUNT(1)
  17.     ----------
  18.             10
  19.       
  20.     SQL> select count(1) from t2;
  21.       
  22.       COUNT(1)
  23.     ----------
  24.          75211
  25.           
  26.     --试验HASH，可以看出hash是存在驱动与被驱动关系，驱动表适合于两表间较小的一个，耗用更少的排序开销；
  27.     SQL> select /*+ leading(t2) use_hash(t2,t1) */t1.status,t2.status
  28.       2 from t1,t2
  29.       3 where t1.object_id=t2.object_id;
  30.       
  31.     STATUS STATUS
  32.     ------- -------
  33.     VALID VALID
  34.     VALID VALID
  35.     VALID VALID
  36.     VALID VALID
  37.     VALID VALID
  38.     VALID VALID
  39.     VALID VALID
  40.     VALID VALID
  41.     VALID VALID
  42.     VALID VALID
  43.       
  44.     10 rows selected.
  45.       
  46.     --看到排序区中排序的开销为3439K；
  47.     SQL> @allstat
  48.       
  49.     PLAN_TABLE_OUTPUT
  50.     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  51.     SQL_ID 315bd4pgdyt86, child number 1
  52.     -------------------------------------
  53.     select /*+ leading(t2) use_hash(t2,t1) */t1.status,t2.status from t1,t2
  54.     where t1.object_id=t2.object_id
  55.       
  56.     Plan hash value: 2959412835
  57.       
  58.     -------------------------------------------------------------------------------------------------------------------------
  59.     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
  60.     -------------------------------------------------------------------------------------------------------------------------
  61.     | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.08 | 1078 | 1071 | | | |
  62.     |* 1 | HASH JOIN | | 1 | 10 | 10 |00:00:00.08 | 1078 | 1071 | 3439K| 1573K| 5617K (0)|
  63.     | 2 | TABLE ACCESS FULL| T2 | 1 | 79628 | 75211 |00:00:00.02 | 1074 | 1071 | | | |
  64.     | 3 | TABLE ACCESS FULL| T1 | 1 | 10 | 10 |00:00:00.01 | 4 | 0 | | | |
  65.     -------------------------------------------------------------------------------------------------------------------------
  66.       
  67.     Predicate Information (identified by operation id):
  68.     ---------------------------------------------------
  69.       
  70.        1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  71.       
  72.     Note
  73.     -----
  74.        - dynamic sampling used for this statement (level=2)
  75.       
  76.       
  77.     25 rows selected.
  78.       
  79.     --而T1表为驱动表时，排序的开销为1206K比上面的3439K小得多；
  80.     SQL> select /*+ leading(t1) use_hash(t2,t1) */t1.status,t2.status
  81.       2 from t1,t2
  82.       3 where t1.object_id=t2.object_id;
  83.       
  84.     STATUS STATUS
  85.     ------- -------
  86.     VALID VALID
  87.     VALID VALID
  88.     VALID VALID
  89.     VALID VALID
  90.     VALID VALID
  91.     VALID VALID
  92.     VALID VALID
  93.     VALID VALID
  94.     VALID VALID
  95.     VALID VALID
  96.       
  97.     10 rows selected.
  98.       
  99.     SQL> @allstat
  100.       
  101.     PLAN_TABLE_OUTPUT
  102.     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  103.     SQL_ID 3zc11bg9gdq31, child number 1
  104.     -------------------------------------
  105.     select /*+ leading(t1) use_hash(t2,t1) */t1.status,t2.status from t1,t2
  106.     where t1.object_id=t2.object_id
  107.       
  108.     Plan hash value: 1838229974
  109.       
  110.     -------------------------------------------------------------------------------------------------------------------------
  111.     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
  112.     -------------------------------------------------------------------------------------------------------------------------
  113.     | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.08 | 1078 | 1071 | | | |
  114.     |* 1 | HASH JOIN | | 1 | 10 | 10 |00:00:00.08 | 1078 | 1071 | 1206K| 1206K| 1126K (0)|
  115.     | 2 | TABLE ACCESS FULL| T1 | 1 | 10 | 10 |00:00:00.01 | 3 | 0 | | | |
  116.     | 3 | TABLE ACCESS FULL| T2 | 1 | 79628 | 75211 |00:00:00.03 | 1075 | 1071 | | | |
  117.     -------------------------------------------------------------------------------------------------------------------------
  118.       
  119.     Predicate Information (identified by operation id):
  120.     ---------------------------------------------------
  121.       
  122.        1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  123.       
  124.     Note
  125.     -----
  126.        - dynamic sampling used for this statement (level=2)
  127.       
  128.       
  129.     25 rows selected.
  130.       
  131.     --而ORACLE在预估COST时第一类的COST也是大于第2类，也就是默认O是会采用第2种方式；
  132.     --第1类的COST为419，而第2类的COST预估为305；
  133.     SQL> set auto traceonly
  134.     SP2-0158: unknown SET autocommit option "traceonly"
  135.     Usage: SET AUTO[COMMIT] { OFF | ON | IMM[EDIATE] | n }
  136.     SQL> set autot traceonly exp
  137.     SQL>
  138.     SQL> select /*+ leading(t2) use_hash(t2,t1) */t1.status,t2.status
  139.       2 from t1,t2
  140.       3 where t1.object_id=t2.object_id;
  141.       
  142.     Execution Plan
  143.     ----------------------------------------------------------
  144.     Plan hash value: 2959412835
  145.       
  146.     -----------------------------------------------------------------------------------
  147.     | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
  148.     -----------------------------------------------------------------------------------
  149.     | 0 | SELECT STATEMENT | | 10 | 360 | | 419 (1)| 00:00:06 |
  150.     |* 1 | HASH JOIN | | 10 | 360 | 2336K| 419 (1)| 00:00:06 |
  151.     | 2 | TABLE ACCESS FULL| T2 | 79628 | 1399K| | 301 (1)| 00:00:04 |
  152.     | 3 | TABLE ACCESS FULL| T1 | 10 | 180 | | 3 (0)| 00:00:01 |
  153.     -----------------------------------------------------------------------------------
  154.       
  155.     Predicate Information (identified by operation id):
  156.     ---------------------------------------------------
  157.       
  158.        1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  159.       
  160.     Note
  161.     -----
  162.        - dynamic sampling used for this statement (level=2)
  163.       
  164.     SQL> select /*+ leading(t1) use_hash(t2,t1) */t1.status,t2.status
  165.       2 from t1,t2
  166.       3 where t1.object_id=t2.object_id;
  167.       
  168.     Execution Plan
  169.     ----------------------------------------------------------
  170.     Plan hash value: 1838229974
  171.       
  172.     ---------------------------------------------------------------------------
  173.     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  174.     ---------------------------------------------------------------------------
  175.     | 0 | SELECT STATEMENT | | 10 | 360 | 305 (1)| 00:00:04 |
  176.     |* 1 | HASH JOIN | | 10 | 360 | 305 (1)| 00:00:04 |
  177.     | 2 | TABLE ACCESS FULL| T1 | 10 | 180 | 3 (0)| 00:00:01 |
  178.     | 3 | TABLE ACCESS FULL| T2 | 79628 | 1399K| 301 (1)| 00:00:04 |
  179.     ---------------------------------------------------------------------------
  180.       
  181.     Predicate Information (identified by operation id):
  182.     ---------------------------------------------------
  183.       
  184.        1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  185.       
  186.     Note
  187.     -----
  188.        - dynamic sampling used for this statement (level=2)
  189.          
  190.     --HASH连接方式默认当然是采用后一种COST较小那类；
  191.     SQL> select t1.status,t2.status
  192.       2 from t1,t2
  193.       3 where t1.object_id=t2.object_id;
  194.       
  195.     Execution Plan
  196.     ----------------------------------------------------------
  197.     Plan hash value: 1838229974
  198.       
  199.     ---------------------------------------------------------------------------
  200.     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  201.     ---------------------------------------------------------------------------
  202.     | 0 | SELECT STATEMENT | | 10 | 360 | 305 (1)| 00:00:04 |
  203.     |* 1 | HASH JOIN | | 10 | 360 | 305 (1)| 00:00:04 |
  204.     | 2 | TABLE ACCESS FULL| T1 | 10 | 180 | 3 (0)| 00:00:01 |
  205.     | 3 | TABLE ACCESS FULL| T2 | 79628 | 1399K| 301 (1)| 00:00:04 |
  206.     ---------------------------------------------------------------------------
  207.       
  208.     Predicate Information (identified by operation id):
  209.     ---------------------------------------------------
  210.       
  211.        1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  212.       
  213.     Note
  214.     -----
  215.        - dynamic sampling used for this statement (level=2)
  216.       
  217.          
  218.     二.排序合并连接方式
  219.     --可以看到无论是哪种表排在前面先执行，其开销都是一样的；也就是说明merge方式是不存在驱动与被驱动的区别关系 ；
  220.     PLAN_TABLE_OUTPUT
  221.     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  222.     SQL_ID 2yw072zjb9h5b, child number 1
  223.     -------------------------------------
  224.     select /*+ leading(t1) use_merge(t2,t1) */t1.status,t2.status from
  225.     t1,t2 where t1.object_id=t2.object_id
  226.       
  227.     Plan hash value: 412793182
  228.       
  229.     --------------------------------------------------------------------------------------------------------------------------
  230.     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
  231.     --------------------------------------------------------------------------------------------------------------------------
  232.     | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.06 | 1077 | 1071 | | | |
  233.     | 1 | MERGE JOIN | | 1 | 10 | 10 |00:00:00.06 | 1077 | 1071 | | | |
  234.     | 2 | SORT JOIN | | 1 | 10 | 10 |00:00:00.01 | 3 | 0 | 2048 | 2048 | 2048 (0)|
  235.     | 3 | TABLE ACCESS FULL| T1 | 1 | 10 | 10 |00:00:00.01 | 3 | 0 | | | |
  236.     |* 4 | SORT JOIN | | 10 | 79628 | 10 |00:00:00.06 | 1074 | 1071 | 2675K| 740K| 2377K (0)|
  237.     | 5 | TABLE ACCESS FULL| T2 | 1 | 79628 | 75211 |00:00:00.02 | 1074 | 1071 | | | |
  238.     --------------------------------------------------------------------------------------------------------------------------
  239.       
  240.     Predicate Information (identified by operation id):
  241.     ---------------------------------------------------
  242.       
  243.        4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  244.            filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  245.       
  246.     Note
  247.     -----
  248.        - dynamic sampling used for this statement (level=2)
  249.       
  250.       
  251.     28 rows selected.
  252.       
  253.     PLAN_TABLE_OUTPUT
  254.     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  255.     SQL_ID csjsgn0hh4dr6, child number 1
  256.     -------------------------------------
  257.     select /*+ leading(t2) use_merge(t2,t1) */t1.status,t2.status from
  258.     t1,t2 where t1.object_id=t2.object_id
  259.       
  260.     Plan hash value: 1792967693
  261.       
  262.     --------------------------------------------------------------------------------------------------------------------------
  263.     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
  264.     --------------------------------------------------------------------------------------------------------------------------
  265.     | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.09 | 1077 | 1071 | | | |
  266.     | 1 | MERGE JOIN | | 1 | 10 | 10 |00:00:00.09 | 1077 | 1071 | | | |
  267.     | 2 | SORT JOIN | | 1 | 79628 | 54 |00:00:00.09 | 1074 | 1071 | 2675K| 740K| 2377K (0)|
  268.     | 3 | TABLE ACCESS FULL| T2 | 1 | 79628 | 75211 |00:00:00.02 | 1074 | 1071 | | | |
  269.     |* 4 | SORT JOIN | | 54 | 10 | 10 |00:00:00.01 | 3 | 0 | 2048 | 2048 | 2048 (0)|
  270.     | 5 | TABLE ACCESS FULL| T1 | 1 | 10 | 10 |00:00:00.01 | 3 | 0 | | | |
  271.     --------------------------------------------------------------------------------------------------------------------------
  272.       
  273.     Predicate Information (identified by operation id):
  274.     ---------------------------------------------------
  275.       
  276.        4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  277.            filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  278.       
  279.     Note
  280.     -----
  281.        - dynamic sampling used for this statement (level=2)
  282.       
  283.     三.嵌套循环连接方式是影响最为明显的，buffers数相差了10倍之多；
  284.       
  285.     PLAN_TABLE_OUTPUT
  286.     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  287.     SQL_ID 35g7vtpc63s04, child number 0
  288.     -------------------------------------
  289.     select /*+ leading(t1) use_nl(t2,t1) */t1.status,t2.status from t1,t2
  290.     where t1.object_id=t2.object_id
  291.       
  292.     Plan hash value: 1967407726
  293.       
  294.     ----------------------------------------------------------------------------------------------
  295.     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
  296.     ----------------------------------------------------------------------------------------------
  297.     | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.11 | 10745 | 10710 |
  298.     | 1 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.11 | 10745 | 10710 |
  299.     | 2 | TABLE ACCESS FULL| T1 | 1 | 10 | 10 |00:00:00.01 | 4 | 0 |
  300.     |* 3 | TABLE ACCESS FULL| T2 | 10 | 1 | 10 |00:00:00.11 | 10741 | 10710 |
  301.     ----------------------------------------------------------------------------------------------
  302.       
  303.     Predicate Information (identified by operation id):
  304.     ---------------------------------------------------
  305.       
  306.        3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  307.       
  308.     Note
  309.     -----
  310.        - dynamic sampling used for this statement (level=2)
  311.          
  312.     PLAN_TABLE_OUTPUT
  313.     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  314.     SQL_ID 1a5dw45kqph9p, child number 0
  315.     -------------------------------------
  316.     select /*+ leading(t2) use_nl(t2,t1) */t1.status,t2.status from t1,t2
  317.     where t1.object_id=t2.object_id
  318.       
  319.     Plan hash value: 4016936828
  320.       
  321.     ----------------------------------------------------------------------------------------------
  322.     | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
  323.     ----------------------------------------------------------------------------------------------
  324.     | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.93 | 226K| 1071 |
  325.     | 1 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.93 | 226K| 1071 |
  326.     | 2 | TABLE ACCESS FULL| T2 | 1 | 79628 | 75211 |00:00:00.06 | 1075 | 1071 |
  327.     |* 3 | TABLE ACCESS FULL| T1 | 75211 | 1 | 10 |00:00:00.74 | 225K| 0 |
  328.     ----------------------------------------------------------------------------------------------
  329.       
  330.     Predicate Information (identified by operation id):
  331.     ---------------------------------------------------
  332.       
  333.        3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
  334.       
  335.     Note
  336.     -----
  337.        - dynamic sampling used for this statement (level=2)
  338.       
  339.       
  340.     25 rows selected.
  341.       
  342.               
  343.     小结：从以上可看出，影响大到小的顺序为nl->hash->merge(不影响);
  344.     应用：日常工作环境中经常可看到，由于nl方式不当而应使用hash的案例，毕竟NL对数据量的大小影响是最为敏感；
  345.     而ORACLE选择NL方式并没有错，错就错在预估值那里，所以当用explain plan for....,autot等方式看并不能看出问题；
  346.     当通过running time的统计信息收集然后select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
  347.     就会发现真正的原因是预估值与实际值的问题相差过大导致的；
  348.     在这种情况下执行计划的绑定是比较实用和有效的；


来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29209863/viewspace-2128167/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 如何能监控rollback的进度
下一篇： Oracle常见hint(leading)
请登录后发表评论 登录
全部评论
hd_system

注册时间：2016-10-12

  • 博文量
    149
  • 访问量
    99037

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593