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 | -------------------------------------------------------------------------------------------------------------------------------
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库