使用HASH代替NEST LOOP

最新推荐文章于 2024-02-28 16:07:24 发布
最新推荐文章于 2024-02-28 16:07:24 发布
阅读量679 收藏
点赞数
分类专栏： 【SQL TUNING】 24号信仰の优化专栏 文章标签： hash nest loop rows返回为1行 rows返回1 嵌套循环的驱动表返回太多
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Skybig1988/article/details/83274731
版权

某系统有一条SQL 执行了十几个小时没出结果~

【SQL复杂  案例简单  已经精通oracle连接方式的可自行绕道别的案例~】

SQL语句和执行计划如下：

select tt.org_type,
       tt.prod_code,
       tt.prod_name,
       tt.BRAND_CODE,
       tt.iscapital,
       tt.cur,
       tt.rijun,
       tt.yuemo,
       tt.jine
  from (select case
                 when t.cust_type = '3' then
                  '个人'
                 when t.cust_type = '0' then
                  case
                    when t.org_type = '2' then
                     '同业'
                    when t.org_type = '1' then
                     '企业'
                    else
                     '机构'
                  end
                 else
                  '不限'
               end org_type,
               t.prod_code,
               t1.prod_name,
               t2.BRAND_CODE,
               case
                 when t2.income_characteristic = '1' then
                  '否'
                 when t2.income_characteristic in ('2', '3') then
                  '是'
               end as iscapital,
               t1.prod_currency cur,
               trunc(sum(case
                           when t.workdate between '20180101' and '20180930' then
                            t.total_vol
                           else
                            0
                         end) / (select (to_date('20180930', 'yyyymmdd') -
                                        to_date('20180101', 'yyyymmdd')) + 1
                                   from dual),
                     3) rijun,
               sum(case
                     when t.workdate =
                          to_char(add_months(to_date(substr('20180930', 1, 6) || '01',
                                                     'yyyymmdd'),
                                             1) - 1,
                                  'yyyymmdd') then
                      t.total_vol
                     else
                      0
                   end) yuemo,
               trunc(sum(case
                           when t.workdate between '20180101' and '20180930' then
                            t.total_vol
                           else
                            0
                         end) / (select (to_date('20180930', 'yyyymmdd') -
                                        to_date('20180101', 'yyyymmdd')) + 1
                                   from dual),
                     3) * nvl(t.nav, '1') jine
          from T5_AAAA_BBBBB_ORG t
          left join T5_AAAA_BBBB t1
            on t.prod_code = t1.prod_code
          left join T5_AAAA_BBBB_ADDITION t2
            on t.prod_code = t2.prod_code
         where (t.workdate between '20180101' and '20180930' or
               t.workdate =
               to_char(add_months(to_date(substr('20180930', 1, 6) || '01',
                                           'yyyymmdd'),
                                   1) - 1,
                        'yyyymmdd'))
           and (1 = 1 OR ('' = '3' and
               (t.cust_type = '' or instr(t.cust_type, '') <> 0 or
               t.cust_type = '-1')) OR
               ('' = '1' and t.cust_type <> '3' and
               (t.org_type = '' or instr(t.org_type, '') <> 0 or
               t.org_type = '-1')) OR
               ('' = '2' and t.cust_type <> '3' and
               (t.org_type = '' or instr(t.org_type, '') <> 0 or
               t.org_type = '-1')))
           and (1 = 1 OR t1.profit_type = '')
           and (1 = 1 OR t2.income_characteristic = '')
           and (1 = 1 OR t1.prod_code = '')
           and (1 = 1 OR t1.prod_name = '')
           and (1 = 1 or t1.prod_lifecycle IN (''))
           and (1 = 1 or t2.prodsection = '')
           and (t.orgno = '6500' OR t.branch_code = '6500' OR
               t.sub_branch_code = '6500' or
               (exists
                (select 1
                    from sys_dict_item d
                   where d.itemval = '6500'
                     and d.itemkey = t.branch_code
                     and d.dict = 'branch_relation') and '' = '1'))
           and (1 = 0 OR '2' in ('0', '2'))
         group by t.prod_code,
                  t1.prod_name,
                  t.org_type,
                  t.cust_type,
                  t2.BRAND_CODE,
                  t2.income_characteristic,
                  t.nav,
                  t1.prod_currency) tt;


Plan hash value: 267004458
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |       |       |   375K(100)|          |
|   1 |  FAST DUAL                      |                          |     1 |       |     2   (0)| 00:00:01 |
|   2 |  FAST DUAL                      |                          |     1 |       |     2   (0)| 00:00:01 |
|   3 |  HASH GROUP BY                  |                          |   183K|    20M|            |          |
|   4 |   CONCATENATION                 |                          |       |       |            |          |
|   5 |    NESTED LOOPS OUTER           |                          |     1 |   118 | 19225   (1)| 00:03:51 |
|   6 |     NESTED LOOPS OUTER          |                          |     1 |    76 | 19223   (1)| 00:03:51 |
|   7 |      TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBBB_ORG        |     1 |    53 | 19222   (1)| 00:03:51 |
|*  8 |       INDEX SKIP SCAN           | PK_T5_AAAA_BBBBB_ORG     |     1 |       | 19221   (1)| 00:03:51 |
|   9 |      TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBB_ADDITION    |     1 |    23 |     1   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN         | PK_T5_AAAA_BBBB_ADDITION |     1 |       |     0   (0)|          |
|  11 |     TABLE ACCESS BY INDEX ROWID | T5_AAAA_BBBB             |     1 |    42 |     2   (0)| 00:00:01 |
|* 12 |      INDEX RANGE SCAN           | PK_T5_AAAA_BBBB          |     1 |       |     1   (0)| 00:00:01 |
|* 13 |    HASH JOIN RIGHT OUTER        |                          | 69859 |  8050K|   134K  (1)| 00:26:56 |
|  14 |     TABLE ACCESS FULL           | T5_AAAA_BBBB_ADDITION    | 22584 |   507K|   208   (1)| 00:00:03 |
|* 15 |     HASH JOIN RIGHT OUTER       |                          | 67177 |  6232K|   134K  (1)| 00:26:53 |
|  16 |      TABLE ACCESS FULL          | T5_AAAA_BBBB             | 22594 |   926K|   243   (1)| 00:00:03 |
|  17 |      TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBBB_ORG        | 67177 |  3476K|   134K  (1)| 00:26:50 |
|* 18 |       INDEX SKIP SCAN           | PK_T5_AAAA_BBBBB_ORG     | 67177 |       | 67143   (1)| 00:13:26 |
|* 19 |    HASH JOIN RIGHT OUTER        |                          |   113K|    12M|   218K  (1)| 00:43:40 |
|  20 |     TABLE ACCESS FULL           | T5_AAAA_BBBB_ADDITION    | 22584 |   507K|   208   (1)| 00:00:03 |
|* 21 |     HASH JOIN RIGHT OUTER       |                          |   108K|     9M|   218K  (1)| 00:43:37 |
|  22 |      TABLE ACCESS FULL          | T5_AAAA_BBBB             | 22594 |   926K|   243   (1)| 00:00:03 |
|  23 |      TABLE ACCESS BY INDEX ROWID| T5_AAAA_BBBBB_ORG        |   108K|  5640K|   217K  (1)| 00:43:35 |
|* 24 |       INDEX SKIP SCAN           | PK_T5_AAAA_BBBBB_ORG     |   109K|       |   109K  (1)| 00:21:49 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access("T"."ORGNO"='6500')
       filter(("T"."ORGNO"='6500' AND (("T"."WORKDATE">='20180101' AND "T"."WORKDATE"<='20180930') 
              OR "T"."WORKDATE"='20180930')))
  10 - access("T"."PROD_CODE"="T2"."PROD_CODE")
  12 - access("T"."PROD_CODE"="T1"."PROD_CODE")
  13 - access("T"."PROD_CODE"="T2"."PROD_CODE")
  15 - access("T"."PROD_CODE"="T1"."PROD_CODE")
  18 - access("T"."SUB_BRANCH_CODE"='6500')
       filter(("T"."SUB_BRANCH_CODE"='6500' AND (("T"."WORKDATE">='20180101' AND 
              "T"."WORKDATE"<='20180930') OR "T"."WORKDATE"='20180930') AND LNNVL("T"."ORGNO"='6500')))
  19 - access("T"."PROD_CODE"="T2"."PROD_CODE")
  21 - access("T"."PROD_CODE"="T1"."PROD_CODE")
  24 - access("T"."BRANCH_CODE"='6500')
       filter(("T"."BRANCH_CODE"='6500' AND (("T"."WORKDATE">='20180101' AND 
              "T"."WORKDATE"<='20180930') OR "T"."WORKDATE"='20180930') AND LNNVL("T"."SUB_BRANCH_CODE"='6500') 
              AND LNNVL("T"."ORGNO"='6500')))

SQL语句中带OR条件一般要特别注意。这里我们先不分析SQL语句   直接看执行计划。

如果你是一个新手  不会特别详细分析执行计划，那么你就猜！！！通过执行计划可以看出来5-12步 rows全是1，这种情况100%有问题，我们猜测这一块有问题  然后我们去验证：用我之前博客发的脚本取一下SQL语句中的对象信息

拥有者	       对象类型	      对象名称	                 是否分区表	   大小MB	         行数	               最近一次分析时间	               分析判断
NNN	          TABLE	          T5_AAAA_BBBB	              NO	          7	             22594	             2018-09-29 22:09:54	    统计信息未过期
NNN	          TABLE	          T5_AAAA_BBBB_ADDITION	      NO	          6	             22584	             2018-09-26 22:15:14	    统计信息未过期
NNN	          TABLE		        T5_AAAA_BBBBB_ORG           NO	          12588	         154698846	         2018-09-30 22:23:18	    统计信息未过期
NNN	          TABLE	          T6_PROD_INFO	              NO	          .125	         78	                 2018-10-10 22:09:34	    统计信息未过期
NNN	          TABLE	          T6_PROD_INFO_ADDITION	      NO	          .125	         78	                 2018-10-09 22:14:43	    统计信息未过期
NNN	          TABLE	          T6_PROD_STOCK_ORG	          NO	          .1875	         1523	               2018-09-30 14:11:21	    统计信息未过期
NNN	          INDEX (UNIQUE)	PK_T5_AAAA_BBBB	            NO	          1	             22594	             2018-09-29 22:09:57	    统计信息未过期
NNN	          INDEX (UNIQUE)	PK_T5_AAAA_BBBB_ADDITION	  NO	          .875	         21717	             2018-06-26 22:08:18	    统计信息过期
NNN	          INDEX (UNIQUE)	PK_T5_AAAA_BBBBB_ORG	      NO	          11177	         154547735	         2018-09-30 22:23:39	    统计信息未过期

ID=6 是NESTED LOOPS OUTER，驱动表是大表，1.5亿多行，去驱动2万多行的表T5_AAAA_BBBB_ADDITION，相当的坑，如果t表过滤后的数据很多，几千万甚至上亿，那此步骤跑不出结果。
综上分析，使用HASH代替执行计划中的嵌套循环? HINT： /*+ full(t) ?use_hash(t,t1)  swap_join_inputs(t1) */

优化完之后SQL 5分钟出结果！！优化后的执行计划如下：

Plan hash value: 2075181580
---------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                       |       |       |       |   446K(100)|          |
|   1 |  FAST DUAL              |                       |     1 |       |       |     2   (0)| 00:00:01 |
|   2 |  FAST DUAL              |                       |     1 |       |       |     2   (0)| 00:00:01 |
|   3 |  HASH GROUP BY          |                       |   183K|    20M|    22M|   446K  (2)| 01:29:17 |
|*  4 |   HASH JOIN RIGHT OUTER |                       |   183K|    20M|       |   441K  (2)| 01:28:18 |
|   5 |    TABLE ACCESS FULL    | T5_AAAA_BBBB_ADDITION | 22584 |   507K|       |   208   (1)| 00:00:03 |
|*  6 |    HASH JOIN RIGHT OUTER|                       |   176K|    15M|       |   441K  (2)| 01:28:16 |
|   7 |     TABLE ACCESS FULL   | T5_AAAA_BBBB          | 22594 |   926K|       |   243   (1)| 00:00:03 |
|*  8 |     TABLE ACCESS FULL   | T5_AAAA_BBBBB_ORG     |   176K|  9117K|       |   441K  (2)| 01:28:13 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T"."PROD_CODE"="T2"."PROD_CODE")
   6 - access("T"."PROD_CODE"="T1"."PROD_CODE")
   8 - filter((("T"."BRANCH_CODE"='6500' OR "T"."SUB_BRANCH_CODE"='6500' OR "T"."ORGNO"='6500') 
              AND (("T"."WORKDATE">='20180101' AND "T"."WORKDATE"<='20180930') OR "T"."WORKDATE"='20180930')))

 

24号信仰
关注
专栏目录
数据工程师必修系列课之SQL优化与PostgreSQL数据库实战
程序员光剑
07-28 1094
数据工程师作为整个企业的数据支撑和运营的重要角色，掌握其相关技能可以实现对数据的整体把握、处理、存储、安全、监控等全生命周期管理。而与此同时，数据分析师也成为各行各业所不可或缺的重要岗位。数据分析师在处理海量数据时，需要有高效的SQL语言水平，对大数据系统的性能优化能力尤其要求。由于数据量越来越大，数据分析师的SQL理解和使用能力要求也越来越强。数据工程师则是基于数据科学和技术领域的专业知识和工具进行更加细致的工作。
jdk11的class反射机制，将newInstance（）方法设置为了不建议使用了，怎么通过反射创建新的对象
极客栈
11-05 5812
下面是jdk11的源代码，看到有个方法 @CallerSensitive @Deprecated(since="9") public T newInstance(){ 。。。。。。 } 这里就意味着，newInstance（）不建议使用 这里看到jdk9就不建议使用了。那么应该用哪个代替呢？ 我们知道创建对象有几种方式 1. new 关键字 ，默认或者显示地调用构造方法实现。 2.反射newInstance。 3.反射方式显示调用构造函数 4.深度拷贝copy 下面是C...
oracle利用绝杀招将hashjoin计划转变为nestloop
viviliving的专栏
08-09 524
以前遇到两个表做关联，每个表都是1亿以上的数据量，执行计划走的hashjoin，直接5个小时没执行完，我看看sql已经优化到没法优化了，但是加hint走nestloop后几分钟就执行完毕了，感觉对于数据量巨大的两个表，加条件的小表作为驱动表nestloop还是很快的 将hashjoin 转为 nested loop hashjoin原文： SELECT KC24.AKB020 AS KC24_AKB020 , KB01.AKB021 AS KB01_AKB021 ...
NEST LOOP改为HASH JOIN 性能提高6倍
ctest011501的博客
08-31 335
NEST LOOP改为HASH JOIN 性能提高6倍Kevin Zou2011-8-31在看系统的AWR report时，发现有个SQL语句出现频率很高，而且都是在消耗资源的top 5内。决定把这个SQL拉出来看看。523,...
nestloop
cs3424307的博客
01-20 181
嵌套循环的程序逻辑相当于for i in .. loop for j in .. loop if (matched) output end if;end loop;end loop;所以它相对于merge join和...
nest loop
ciyixi4265的博客
03-10 477
nested loop join 一般适和大小表，且大表有index，全表scan小表，然后按index抽取大表匹配记录，返会第一条记录快。 1.建立测试表 SQL> create ta...
matlab代码续行-Matlab-nestloop:用于将多个循环转换为一个函数的工具，仅需输入循环变量的范围即可
05-26
matlab 代码续行 Matlab-nestloop a tool used for transforming several loops to a function which is only needed to input the range of loop variables 中文介绍 是不是经常遇到，需要写许多个for循环，每一层for循环都要记录中间的结果，光是中间结果的变量名称都容易记混。 有时仅仅考虑某两个循环变量的关系时，又需要注释掉其他的for循环，导致代码改来改去。 有时候想用parfor做并行计算，但是由于循环体内公用了一些变量，使得无法使用parfor。 有时候循环时候很长，但是想记录循环的位置，临时保存结果，下次可以断点继续。 这些都可以通过nestloop这个函数实现啦！！ relationCell=nestloop2(times,vars,baseFunc,varargin) times --最内层的循环,表示每种输入参数的原子baseFunc操作需要重复的次数，最少为1 vars --需要迭代的变量以及常量值，默认所有的数组都是需要遍历的，如果想传入一
mysql hash join_MySQL8.0 新特性 Hash Join
weixin_35874865的博客
01-19 767
概述&背景MySQL一直被人诟病没有实现HashJoin，最新发布的8.0.18已经带上了这个功能，令人欣喜。有时候在想，MySQL为什么一直不支持HashJoin呢？我想可能是因为MySQL多用于简单的OLTP场景，并且在互联网应用居多，需求没那么紧急。另一方面可能是因为以前完全靠社区，这种演进速度毕竟有限，Oracle收购MySQL后，MySQL的发版演进速度明显加快了很多。HashJ...
mysql分片分桶_MySQL8.0 新特性 Hash Join
weixin_34638409的博客
02-02 473
概述&背景MySQL一直被人诟病没有实现HashJoin，最新发布的8.0.18已经带上了这个功能，令人欣喜。有时候在想，MySQL为什么一直不支持HashJoin呢？我想可能是因为MySQL多用于简单的OLTP场景，并且在互联网应用居多，需求没那么紧急。另一方面可能是因为以前完全靠社区，这种演进速度毕竟有限，Oracle收购MySQL后，MySQL的发版演进速度明显加快了很多。HashJ...
Drools4.0官方使用手册中文
u011930916的博客
03-20 4549
第一章. Drools 4.0 发布版标注1.1.  Drools 4.0的新内容Drools4.0是从之前的Drools3.0.x系列以来的一次主要更新。在语言表达式，引擎性能和工具实用性方面都有一整套的新特性开发完成。下面列出一些最引人注意的更新列表。1.1.1. 语言表达式增强· 新的条件元素: from, collect, accumulate 和forall· 新的字段约束操作: not...
nestloop系列
fgh123的博客
05-07 535
文章目录create_nestloop_planbuild_path_list是啥意思 create_nestloop_plan build_path_list是啥意思 可以看出来这个东西主要跟path->pathtarget有关 而记得我们在创建nestpath的时候这个值是根据这个赋值语句来的 在create_nestloop_path里面的 path->path....
【OpenGauss源码学习 —— 执行算子（Nest Loop 算子）】
最新发布
qq_43899283的博客
02-28 622
连接算子用于处理表关联，openGauss支持12种连接类型（等），提供了3算子；其中，在先前的学习中，【OpenGauss源码学习 —— 执行算子（hash join 算子）】一文中详细介绍了hash join算子的执行过程。【OpenGauss源码学习 —— 执行算子（Merge Join 算子）】一文中学习了Merge Join算子的执行过程。本文则来继续学习另一个扫描算子Nest Loop算子。Nest Loop可以处理各种连接类型，包括内连接左连接右连接和全连接。对于小型数据集或者。
NEST LOOP JOIN 和MERGE JOIN
godfather00的专栏
10-26 900
一、        嵌套循环连接（Nested Loop）： 嵌套循环连接的工作方式是这样的： 1、        Oracle首先选择一张表作为连接的驱动表，这张表也称为外部表（Outer Table）。由驱动表进行驱动连接的表或数据源称为内部表（Inner Table）。 2、        提取驱动表中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Or
Nest Loop Join嵌套循环连接
hxxjxw的博客
10-12 506
Nest Loop Join的操作过程很简单，很像我们最简单的排序检索算法，两层循环结构。进行连接的两个数据集合（数据表）分别称为外侧表（驱动表）和内侧表（非驱动表）。 首先处理驱动表中每一行符合条件的数据，之后的每一行数据和非驱动表进行连接匹配操作，直到循环结束 ...
Oracle表连接操作——Nest Loop Join（嵌套循环）
thy822的专栏
05-09 5199
关系型数据库并不是最早出现的数据库表现形式，之前还存在层次、网状数据库结构。随着关系型数据库的出现，以数据表的方式进行信息留存的方案迅速发展起来。关系型数据库的重要元素包括数据表和表连接，借助各种类型的表连接，可以将平铺直叙的信息加以组装拼接。  在Oracle数据库中，应对不同的连接情况和数据特征，数据库CBO会生成不同的执行计划和连接操作。本篇介绍各种连接中的一种——Nest Loop Joi...
Nested loops、Hash join、Sort merge join（三种连接类型原理、使用要点）
张冲andy的博客
04-28 2214
nested loop 嵌套循环（原理）： oracle从较小结果集（驱动表、也可以被称为outer）中读取一行，然后和较大结果集（被侦查表，也可以叫做inner）中的所有数据逐条进行比较（也是等值连接，也可以是非等值连接），如果符合规则，就放在结果集中，然后取驱动表的下一条数据继续循环，直到结束。 简单的理解就是：从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个
nest loop 优化1
csmj52882的博客
01-18 794
nest loop 在如下场景 性能会明显高于hash join 1、驱动结果集较小 2、被驱动表有高效索引 这种情况下，优化思路往nest loop方向优化 如下为一套数据库，top logicalread sql...
oracle 表连结的三种方式（nest loop,sort merge ,hash join)
tlx20093A的专栏
11-28 966
http://oracle.chinaitlab.com/optimize/714876.html
nest loop 与hash join
03-16
哈希连接是一种更高效的查询算法，它使用哈希表来加速查询。在这种算法中，首先将一个表的所有行存储在哈希表中，然后遍历另一个表的每一行，并在哈希表中查找匹配的行。这种算法的时间复杂度为O(n)，因此它在处理...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值