Filter的一个测试

来源：互联网发布：两个数字匹配算法编辑：程序博客网时间：2024/06/05 07:55
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(*)----------       519SQL> 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(select 1           from t2          where t2.owner = t1.ownergroup by t2.object_type         having count(*) >= 10)    andt1.object_type = 'TABLE'Plan hash value: 2091197476-------------------------------------------------------------------------------------------------------------------| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem|  1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:00:00.01 |     268 ||       |         ||   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:00.01 |     268 ||       |         ||*  2 |   FILTER              |      |      1 |        |    519 |00:00:00.01 |     268 ||       |         ||*  3 |    TABLE ACCESS FULL  | T1   |      1 |    587 |    522 |00:00:00.01 |      67 ||       |         ||*  4 |    FILTER             |      |      3 |        |      2 |00:00:00.01 |     201 ||       |         ||   5 |     HASH GROUP BY     |      |      3 |     50 |      8 |00:00:00.01 |     201 |  1301K|  1301K| 1074K (0)||*  6 |      TABLE ACCESS FULL| T2   |      3 |     50 |   3449 |00:00:00.01 |     201 ||       |         |-------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter( IS NOT NULL)   3 - filter("T1"."OBJECT_TYPE"='TABLE')   4 - filter(COUNT(*)>=10)   6 - filter("T2"."OWNER"=:B1)Note-----   - dynamic sampling used for this statement (level=2)已选择33行。①Starts的意思是执行了几次，首先2这个地方执行了1次，是对t1进行全表扫描，返回了522行记录，测试如下:SQL> select count(*) from t1 where t1.object_type = 'TABLE';  COUNT(*)----------       522②看 一下4这个结点，执行了3次， 测试：SQL> select distinct owner from t1 where t1.object_type = 'TABLE';OWNER------------------------------OUTLNSYSTEMSYS总结：1.Filter就是外部查询返回多少个唯一值，内部查询就访问多少次。2.产生Filter的方式：①exists里有group by xx having xx②exists里有rownum 或者是start with时会产生filter.3.filter的执行计划一般是不好的，因为外部返回的数据很大的话，就会导致内部查询扫描很多次。改写exists：这样改写的原因是owner是传值，其实也就是相当于对owner分组    select /*+ gather_plan_statistics */ count(*)  from t1 where  owner in (select owner          from t2         group by t2.object_type,owner        having count(*) >= 10)   and t1.object_type = 'TABLE';   select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));      PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  c07dgxq09xqga, child number 0-------------------------------------select /*+ gather_plan_statistics */  count(*)   from t1  where  ownerin (select owner           from t2          group byt2.object_type,owner         having count(*) >= 10)    andt1.object_type = 'TABLE'Plan hash value: 3015395397-------------------------------------------------------------------------------------------------------------------------| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |          |      1 |        |      1 |00:00:00.01 |     134 |       |       |          ||   1 |  SORT AGGREGATE         |          |      1 |      1 |      1 |00:00:00.01 |     134 |       |       |          ||*  2 |   HASH JOIN SEMI        |          |      1 |    587 |    519 |00:00:00.01 |     134 |  2293K|  2293K|  561K (0)||   3 |    JOIN FILTER CREATE   | :BF0000  |      1 |    587 |    522 |00:00:00.01 |      67 |       |       |          ||*  4 |     TABLE ACCESS FULL   | T1       |      1 |    587 |    522 |00:00:00.01 |      67 |       |       |          ||   5 |    VIEW                 | VW_NSO_1 |      1 |   5002 |     10 |00:00:00.01 |      67 |       |       |          ||*  6 |     FILTER              |          |      1 |        |     10 |00:00:00.01 |      67 |       |       |          ||   7 |      HASH GROUP BY      |          |      1 |   5002 |     21 |00:00:00.01 |      67 |  1214K|  1214K| 1249K (0)||   8 |       JOIN FILTER USE   | :BF0000  |      1 |   5002 |   3449 |00:00:00.01 |      67 |       |       |          ||*  9 |        TABLE ACCESS FULL| T2       |      1 |   5002 |   3449 |00:00:00.01 |      67 |       |       |          |-------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OWNER"="OWNER")   4 - filter("T1"."OBJECT_TYPE"='TABLE')   6 - filter(COUNT(*)>=10)   9 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"OWNER"))Note-----   - dynamic sampling used for this statement (level=2)已选择36行。
0 0