filter与nested loops的区别

最新推荐文章于 2023-10-17 16:48:09 发布
最新推荐文章于 2023-10-17 16:48:09 发布
阅读量1.4k 收藏
点赞数
分类专栏： Oracle SQL优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/stevendbaguo/article/details/8279796
版权

create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'test1',cascade => true);
exec dbms_stats.gather_table_stats(user,'test2',cascade => true);

      unnest我们称为对子查询展开，是别让子查询孤单地嵌套(nest)在里面。所以un_unnest双重否定代表肯定，即让子查询不展开，让它嵌套(nest)在里面。

select test1.object_id from test1 where exists
   (select 1 from test2 where test1.object_id=test2.object_id*10);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 |   328   (3)| 00:00:04 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    10 |   328   (3)| 00:00:04 |
|   2 |   TABLE ACCESS FULL| test1 | 51318 |   250K|   163   (2)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| test2 | 51319 |   250K|   163   (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("test1"."OBJECT_ID"="test2"."OBJECT_ID"*10)
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1750  consistent gets
          0  physical reads
          0  redo size
      72669  bytes sent via SQL*Net to client
       4074  bytes received via SQL*Net from client
        336  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5020  rows processed
      
10046跟踪：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      336      0.07       0.07          0       1750          0        5020
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      338      0.07       0.09          0       1750          0        5020

 

select test1.object_id from test1 where exists
  (select /*+no_unnest*/ 1 from test2 where test1.object_id=test2.object_id*10)
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 | 81532   (1)| 00:16:19 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| test1 | 51318 |   250K|   163   (2)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| test2 |     2 |    10 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "test2" "test2"
              WHERE "test2"."OBJECT_ID"*10=:B1))
   3 - filter("test2"."OBJECT_ID"*10=:B1)

----------------------------------------------------------
          1  recursive calls
          0  db block gets
   33058028  consistent gets
          0  physical reads
          0  redo size
      72669  bytes sent via SQL*Net to client
       4074  bytes received via SQL*Net from client
        336  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5020  rows processed
     
10046跟踪：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      336    393.10     394.32          0   33058028          0        5020
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      338    393.10     394.32          0   33058028          0        5020


select /*+use_nl(test1 test2)*/ test1.object_id from test1, test2
   where test1.object_id = test2.object_id * 10;
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 51323 |   501K|  8279K  (2)| 27:35:54 |
|   1 |  NESTED LOOPS      |      | 51323 |   501K|  8279K  (2)| 27:35:54 |
|   2 |   TABLE ACCESS FULL| test1 | 51318 |   250K|   163   (2)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| test2 |     1 |     5 |   161   (2)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("test1"."OBJECT_ID"="test2"."OBJECT_ID"*10)
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   36437152  consistent gets
          0  physical reads
          0  redo size
      72669  bytes sent via SQL*Net to client
       4074  bytes received via SQL*Net from client
        336  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       5020  rows processed
      
10046跟踪：      
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      336    380.62     381.39          0   36437152          0        5020
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      338    380.62     381.39          0   36437152          0        5020

         filter与nested loop相似，但它的独特之处在于会维护一个hash table。

        举例，如果test1里取出object_id=1，那么对于test2来说即select 1 from test2 where test2.object_id*10=1,如果条件满足，那么对于子查询，输入输出对，即为(1（test1.object_id）,1（常量）)。它存储在hash table里，并且由于条件满足，test1.object_id=1被放入结果集。然后接着从test1取出object_id=2，如果子查询依旧条件满足，那么子查询产生另一个输入和输出，即(2,1)，被放入hash table里；并且test1.object_id=2被放入结果集。接着假设test1里有重复的object_id，例如我们第三次从test1取出的object_id=2,那么由于我们对于子查询来说，已经有输入输出对(2,1)在hash table里了，所以就不用去再次全表扫描test2了，ORACLE非常聪明地知道object_id=2是结果集。

           filter和neested loop相比,省去了一次全表扫描test2。这个hash table是有大小限制的，当被占满的时候，后续新的test1.object_id的FILTER就类似neested loop了。

          由此可见，从buffer gets层面上来看，FILTER是应该优于nested loop的，尤其当外部查询需要传递给子查询的输入（此例中为test1.object_id）的distinct value非常小时，FILTER就会显得更优。

select test1.object_id from test1 where exists
  (select /*+no_unnest*/ 1 from test2 where test1.object_type=test2.object_type);
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1426 | 19964 |   235   (2)| 00:00:03 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| test1 | 51318 |   701K|   163   (2)| 00:00:02 |
|*  3 |   TABLE ACCESS FULL| test2 |     2 |    18 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "test2" "test2"
              WHERE "test2"."OBJECT_TYPE"=:B1))
   3 - filter("test2"."OBJECT_TYPE"=:B1)
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      10977  consistent gets
          0  physical reads
          0  redo size
     743550  bytes sent via SQL*Net to client
      38031  bytes received via SQL*Net from client
       3423  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      51318  rows processed
     
10046跟踪：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3423      0.18       0.19          0      10977          0       51318
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3425      0.18       0.19          0      10977          0       51318

       同样是test1和test2的全表扫描后的FILTER操作，却因为传给子查询的输入的distinct value的差别，两者相差的consistent gets却如此巨大，这跟neested loop是完全不一样的。

       对于如此的两个全表扫描的结果集，使用hash join是最佳方法。
select test1.object_id from test1 where exists
  (select 1 from test2 where test1.object_type=test2.object_type);
-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 41340 |   928K|       |   445   (3)| 00:00:06 |
|*  1 |  HASH JOIN RIGHT SEMI|      | 41340 |   928K|  1056K|   445   (3)| 00:00:06 |
|   2 |   TABLE ACCESS FULL  | test2 | 51319 |   451K|       |   163   (2)| 00:00:02 |
|   3 |   TABLE ACCESS FULL  | test1 | 51318 |   701K|       |   163   (2)| 00:00:02 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("test1"."OBJECT_TYPE"="test2"."OBJECT_TYPE")
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4796  consistent gets
          0  physical reads
          0  redo size
     743550  bytes sent via SQL*Net to client
      38031  bytes received via SQL*Net from client
       3423  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      51318  rows processed
10046跟踪：
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3423      0.21       0.15          0       4796          0       51318
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3425      0.21       0.15          0       4796          0       51318

深圳gg
关注
专栏目录
统计信息过期导致SQL进行NESTED LOOPS查询缓慢
cuanhuai4935的博客
04-26 1028
今天qq上一好友发过来一个sql让我帮他看看，说这个sql加上一个条件查询时间在3~4秒左右，而不加上这个条件则非常快，正好这段时间也在学习优化，就看看问题所在。 sql语句大致如下： SQL&gt...
SQ优化之Nested Loops
05-16 3283
开发给的sql，跑得慢，需要优化，sql如下： SELECT/*+INDEX(N IND1)*/ n.CNTR_N as cntrNoForAutitLog, n.CNTR_N as containerNumber, n.CNTR_TYPE_C as containerType, n.LENGTH_Q as containerLength, n.STRUCTURE_STATUS_C as
Oracle的Filter,Nest loop,Merge sort join和Hash join(原创)
czmmiao的专栏
02-18 348
 Merge Sort Join按照Merge Sort Join连接的两表地位完全相同。这种算法会把每个表按照连接列进行排序，生成两个排序集。然后对两个排序集进行一次遍历便可以得到最终结果集。这个算法的特点是，每个表都需要排序，排序后都需要遍历一次。 以下面的例子说明，Merge Sort Join的执行过程如下：SQL> explain plan for select /*+ us...
HASH JOIN ,MERGE JOIN ,NESTED LOOP用法效率比较
我思，故我在！--My data life
12-19 1万+
概述： NESTED LOOP: 对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO默认的驱动表，使用USE_NL
oracle 表连接方式： nested loop 嵌套循环和Hash Join的比较
daf380的专栏
10-08 1万+
一、创建两张实验用表：wireless_site.merchant和wireless_site.bb SQL> select count(*) from wireless_site.merchant;   COUNT(*) ----------      14005 SQL> SQL> select count(*) from wireless_site.clickthr...
Elasticsearch nested sort filter 嵌套排序问题及解决
zhanlanmg的专栏
10-20 2万+
官方解释首先来看一下官方文档：Sorting by Nested Fields 看不懂的没关系，它其实就只有两句是有用的，就是那两段代码。索引PUT /my_index/blogpost/2 { "title": "Investment secrets", "body": "What they don't tell you ...", "tags": [ "shares", "eq
SpringBoot之Filter拦截器的使用 — 获取requestBody解决nested exception is java.io.IOException: Stream closed
liaonanfeng88的CSDN博客
10-17 3万+
直接问题处理过程； 异常如下： 异常摘要：I/O error while reading input message; nested exception is java.io.IOException: Stream closed org.springframework.http.converter.HttpMessageNotReadableException: I/O error whil...
SQL优化—nested loop优化
weixin_34066347的博客
08-14 1314
跑批时间段22:00-23:00，生成AWR报告 分析sql:SQL_ID='5hfw4smzs2pqw' 执行计划： SQL> select * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5hfw4smzs2pqw',NULL,'ALL')); SQL_ID 5hfw4smzs2pqw, child number 0 ...
Nested loop（PostgreSQL 14 Internals翻译版）
最新发布
数据源的港湾
10-17 1130
这里，Nested Loop节点遍历外层集合(tickets)的行，并对每一行搜索内部集合(ticket_flights)的对应行，将机票号(t.t ticket_no)作为参数传递给条件。可以更准确地估计列表中出现的行的选择性，只有剩下的行必须依赖基于均匀分布的计算。如果对Memoize节点的调用显示传递的参数值与已经缓存的行对应，则这些行将被传递到父节点(Nested Loop)，而不检查子节点。由于连接的表是通过外键连接的，因此选择性估计依赖于子表的每一行在父表中恰好有一个匹配行的事实。
SQL 执行计划的固定手段 --- Hint 方式（Nested Loop Join）
weixin_50513167的博客
04-18 1712
Nested Loop Join 指定时用到的 Hint 和 Hash Join 相对应的，通常，利用索引时一般会用到 Nested Loop Join。 下面我们来继续看看如何控制 Nested Loop Join 的使用，以及 Nested Loop Join 的顺序。 LEADING Hint （指定 Nested Loop Join 顺序） USE_NL (指定使用 Nested Loop Join) 依然通过例子来进行说明。 准备： drop table t1 purge; drop tabl
ElasticSearch – nested mappings and filters
Zhangxi
11-18 1205
还有一个情况下，我们需要帮助ElasticSearch了解我们的数据结构，以便能够充分进行查询 - 复杂对象数组的时候。 按理说的ElasticSearch的最佳功能之一是，它使我们能够索引和搜索之间复杂的JSON对象。我们不局限于领域的平面列表，但可以与对象图工作，就像我们使用面向对象语言编程时。 然而，有一种情况，我们需要帮助ElasticSearch了解我们的数据结构，以便能
Elasticsearch嵌套式对象Nested分析
热门推荐
Elasticsearch
03-15 3万+
nested结构是Elasticsearch提供关系存储的一种特殊的结构,是NOSQL的一种高级特性,在传统的关系型sql中,很难做到一行记录中存储某个实体以及附属的内容，比如某个用户下评论数据，或某个订单下的所有商品等这种关系比较强的内容。当然传统sql也能做到,比如我们当想存储一个订单和该订单下的商品内容。我们可以定义一个text类型的字段,以json的方式存储不同的商品信息,但是这样有一个致命
10 嵌套循环(NESTED LOOP)--优化主题系列
leo0805的博客
09-30 7260
Oracle从较小结果集(驱动表/外部表)中读取一行，然后和较大结果集(被探查表/内部表)中的所有数据逐条进行比较(嵌套循环可以用于非等值连接)，如果符合规则，就放入结果集中，然后取较小结果集的下一条数据继续进行循环，直到结束。嵌套循环只适合输出少量结果集或者是用于快速输出结果集。
【oracle概念】表联结方式
践行见远
01-29 778
表联结方式（逻辑分类与算法分类）          多表联结的方式根据业务逻辑的需要，会有很多种，比如          相等联结、不等联结、交叉联结、外联结、自联结、反联结（anti）和半联结（semi）等。          但实际在oracle内部执行的时候，它只有三种联结算法：          嵌套循环联结（nestedloop），排序合并联结（sort-merge）和哈希联结（
oracle执行计划中NESTED LOOPS SEMI （即半嵌套循环）的解释
haiross的专栏
12-25 8419
在存在in的子查询的SQL语句和存在EXISTS的相关子查询的SQL语句的执行计划里，有NESTED LOOPS SEMI （即半嵌套循环）。 所谓的NESTED LOOPS SEMI （即半嵌套循环），就是 the out query stops evaluating （评价，求…的数值）the result set of the inner query when the first
left join、right join和join的区别
Note from 2021.1 base 杭州
09-29 5273
一张图解图，如下： 部分同学看后可能有类似思考，在真实的表查询当中，哪部分数据属于图中示意的交集？我是这么理解的，把查询SQL的ON后规定的 A.Key=B.Key ，想象为两表的主键和外键，是不是就一目了然了呢(#^.^#) 但要理解图中的含义，最终还是要自己动手去玩一下的，思否一篇文章的案例非常棒，下面引用展示：首先，建两张表，第一张表名为A，第二张表名为B 一、left join 顾名思义“左连接”，表A左连接表B，表示以左边的表A为主，关联查询表B的数据。查出来的结果将会显示左表.
oracle (+)的,Oracle中(+)的作用
weixin_31916045的博客
04-03 754
(+)放在右边表示左连接，右面是附加信息，等价于left joinselect A.*,B.* from A left join B on A.id=B.id;等价于select A.*,B.* from A,B where A.id=B.id(+);(+)放在左面边表示右连接，左面是附加信息，等价于right joinselect A.*,B.* from A right join B on...
Nested Loop，Hash Join，Merge Join 对比介绍
freedom_824的博客
07-25 1627
表的三种关联方式： nested loop：从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表........就是一个二重循环。（特点A表驱动表要小，内表b关联的字段需要有索引） hash join：将A表按连接键计算出一个hash表，然后从B表一条条抽取记录，计算hash值，根据hash到A表的hash来匹配符合条件的记录。（适合等值） sort merge join：将...
Oracle执行计划filter下多个节点的优化
coco3600的博客
07-03 672
Oracle执行计划filter下多个节点的优化 FILTER操作是执行计划中常见的操作，这种操作有两种情况： l只有一个子节点，那么就是简单过滤操作。 ...
Oracle nested loops 怎么优化
05-30
因为nested loops的性能与内部表的大小有关，所以可以通过对内部表添加索引或者使用更优的查询条件来减小内部表的数据量，从而提高查询性能。 2. 将大表作为外部表。由于nested loops是在外部表上进行遍历，因此...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值