filter与nested loops的区别

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值