Filter的一个测试:
创建测试表 t1 和t2.
create table t1 as select * from dba_objects where rownum<=5000;
create table t2 as select * from dba_objects where rownum<=5000;
执行该测试SQL,这里使用了提示来收集真实的执行计划
select /*+ gather_plan_statistics */
count(*)
from t1
where exists (select 1
from t2
where t2.owner = t1.owner
group by t2.object_type
having count(*) >= 10)
and t1.object_type = 'TABLE';
set autot off;
select /*+ gather_plan_statistics */
count(*)
from t1
where exists (select 1
from t2
where t2.owner = t1.owner
group by t2.object_type
having count(*) >= 10)
and t1.object_type = 'TABLE';
COUNT(*)
----------
519
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
---------------
SQL_ID 49t0m3dv35346, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from t1 where exists
(
Filter的一个测试
最新推荐文章于 2021-12-17 22:05:55 发布
本文通过一个具体的SQL测试案例,分析了Filter在执行计划中的作用和影响。Filter操作出现在exists子查询中,当外部查询返回值多时,可能导致内部查询重复扫描。优化策略包括改写exists为in子查询,以减少Filter的负面影响。测试结果显示,优化后的SQL执行效率得到提升。
摘要由CSDN通过智能技术生成