Oracle Filter执行计划

复制代码
实验数据准备
SQL> create table scott.htz1 as select * from dba_objects;
SQL> create table scott.htz2 as select * from dba_objects;
SQL> create table scott.htz3 as select * from dba_objects;
SQL> create index scott.ind_htz3_object_owner on scott.htz3(object_id,owner);
set linesize 500
set termout off
alter session set statistics_level=all;
SQL> host cat plan.sql
set linesize 200 pagesize 999


测试一、驱动表无重复值
SQL>
 select *
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in (select object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
Plan hash value: 1177288075
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                       |      1 |        |   772 (100)|          |      0 |00:00:00.06 |    1519 |
|*  1 |  HASH JOIN             |                       |      1 |     14 |   772   (1)| 00:00:10 |      0 |00:00:00.06 |    1519 |
|*  2 |   HASH JOIN RIGHT SEMI |                       |      1 |     14 |   424   (1)| 00:00:06 |      0 |00:00:00.06 |    1519 |
|*  3 |    INDEX FAST FULL SCAN| IND_HTZ3_OBJECT_OWNER |      1 |     14 |    77   (0)| 00:00:01 |     64 |00:00:00.02 |     270 |
|*  4 |    TABLE ACCESS FULL   | HTZ1                  |      1 |  37820 |   347   (1)| 00:00:05 |  37820 |00:00:00.02 |    1249 |
|   5 |   TABLE ACCESS FULL    | HTZ2                  |      0 |  97015 |   347   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   2 - access("A"."OBJECT_ID"="OBJECT_ID")
   3 - filter("C"."OWNER"='SCOTT')
   4 - filter("A"."OWNER"='SYS')

--想整一个 filter执行计划! 不出来,说明Oracle 版本升级后优化器确实存在内部提升!
 select  * 
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
 (select /*+ NO_USE_HASH(c) */ object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));

参考
https://www.cndba.cn/cndba/dave/article/4352

 select  /*+ optimizer_features_enable('10.2.0.5') */ * 
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
 (select object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
Plan hash value: 286543034
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                       |      1 |        | 80894 (100)|          |      0 |00:00:00.78 |   41608 |    926 |
|*  1 |  FILTER             |                       |      1 |        |            |          |      0 |00:00:00.78 |   41608 |    926 |
|*  2 |   HASH JOIN         |                       |      1 |  80459 |  2533   (1)| 00:00:31 |  87117 |00:00:00.52 |    2498 |    926 |
|   3 |    TABLE ACCESS FULL| HTZ1                  |      1 |  80459 |   347   (1)| 00:00:05 |  87119 |00:00:00.05 |    1249 |      0 |
|   4 |    TABLE ACCESS FULL| HTZ2                  |      1 |  97015 |   347   (1)| 00:00:05 |  87120 |00:00:00.08 |    1249 |    926 |
|*  5 |   FILTER            |                       |  87117 |        |            |          |      0 |00:00:00.14 |   39110 |      0 |
|*  6 |    INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER |  37819 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.10 |   39110 |      0 |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   5 - filter(:B1='SYS')
   6 - access("OBJECT_ID"=:B1 AND "C"."OWNER"='SCOTT')

select count(*) from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id;
  COUNT(*)
----------
     87117
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id);
  COUNT(*)
----------
     87117
     
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS');     
  COUNT(*)
----------
     37819
     
!!!Filter 驱动表的需要循环执行的次数,是驱动结果集的数量!
     
--index scott.ind_htz3_object_owner    [object_id,owner]
select count(*) from (
select object_id,owner from scott.htz3 group by object_id,owner);
  COUNT(*)
----------
     87121

测试二、驱动表存在重复值
SQL> insert into scott.htz1 select * from scott.htz1;
commit;

 select  /*+ optimizer_features_enable('10.2.0.5') */ * 
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
 (select object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                       |      1 |        |   291K(100)|          |      0 |00:00:01.13 |   86565 |
|*  1 |  FILTER             |                       |      1 |        |            |          |      0 |00:00:01.13 |   86565 |
|*  2 |   HASH JOIN         |                       |      1 |    288K|  5409   (1)| 00:01:05 |    174K|00:00:00.69 |    3767 |
|   3 |    TABLE ACCESS FULL| HTZ2                  |      1 |  97015 |   347   (1)| 00:00:05 |  87120 |00:00:00.03 |    1249 |
|   4 |    TABLE ACCESS FULL| HTZ1                  |      1 |    324K|   694   (1)| 00:00:09 |    174K|00:00:00.06 |    2518 |
|*  5 |   FILTER            |                       |    173K|        |            |          |      0 |00:00:00.26 |   82798 |
|*  6 |    INDEX RANGE SCAN | IND_HTZ3_OBJECT_OWNER |  74784 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.17 |   82798 |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   5 - filter(:B1='SYS')
   6 - access("OBJECT_ID"=:B1 AND "C"."OWNER"='SCOTT')

select count(*) from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id;
  COUNT(*)
----------
    174234
    
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id);
  COUNT(*)
----------
     87117
     
select count(*) from (
select distinct a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS');          
  COUNT(*)
----------
     37819
     
select count(*) from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS');
  COUNT(*)
----------
     75638

select count(*) from scott.htz3;
87121
select count(*) from scott.htz3 where owner='SCOTT';
64

select count(*) from (
select tmp_b.object_id from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id) tmp_a,scott.htz1 tmp_b 
 where tmp_a.object_id=tmp_b.object_id and tmp_b.owner='SYS');
  COUNT(*)
----------
    151276
select count(*) from (
select  distinct tmp_b.object_id from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id) tmp_a,scott.htz1 tmp_b 
 where tmp_a.object_id=tmp_b.object_id and tmp_b.owner='SYS');
  COUNT(*)
----------
     37819
select count(*) from (
select  tmp_b.object_id from (
select a.object_id from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id and a.owner='SYS') tmp_a,scott.htz1 tmp_b 
 where tmp_a.object_id=tmp_b.object_id );
     
 select  * 
from scott.htz1 a, scott.htz2 b
where a.object_id = b.object_id
and a.object_id in
 (select /*+ no_unnest */ object_id
from scott.htz3 c
where c.owner = 'SCOTT'
and a.owner = 'SYS')
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                  | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                       |      1 |        |  2663 (100)|          |      0 |00:00:00.53 |   42877 |
|*  1 |  FILTER             |                       |      1 |        |            |          |      0 |00:00:00.53 |   42877 |
|*  2 |   HASH JOIN         |                       |      1 |   3246 |  1040   (1)| 00:00:13 |  75638 |00:00:00.33 |    3767 |
|*  3 |    TABLE ACCESS FULL| HTZ1                  |      1 |   3246 |   692   (1)| 00:00:09 |  75640 |00:00:00.04 |    2518 |
|   4 |    TABLE ACCESS FULL| HTZ2                  |      1 |  97015 |   347   (1)| 00:00:05 |  87120 |00:00:00.04 |    1249 |
|*  5 |   INDEX RANGE SCAN  | IND_HTZ3_OBJECT_OWNER |  37819 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.12 |   39110 |
-------------------------------------------------------------------------------------------------------------------------------
复制代码

 

posted on   数据与人文  阅读(5)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

点击右上角即可分享
微信分享提示