请问Filter 是一种什么样的操作，它的机制是什么？

Yong Huang

原帖由 zjuzsf 于 2008-5-7 07:04 发表
在11g里面 not in有了比较大变化，以前版本如果返回列可以为空不能使用anti join，在11g里面可以为null的也能使用anti join了
...
T@ORCL>select count(*) from tt WHERE OBJECT_ID NOT IN (SELECT OBJECT_ID FROM TT);
  COUNT(*)
----------
         0
Elapsed: 00:00:00.12
Execution Plan
----------------------------------------------------------
Plan hash value: 1524786397
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    26 |       |   743   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE     |      |     1 |    26 |       |            |          |
|*  2 |   HASH JOIN ANTI NA |      |     1 |    26 |  1784K|   743   (1)| 00:00:09 |
|   3 |    TABLE ACCESS FULL| TT   | 72969 |   926K|       |   284   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| TT   | 72969 |   926K|       |   284   (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"="OBJECT_ID")
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2032  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

filter 的性能实际上跟列值distinct数有关，oracle在执行的时候实际上做了很大优化，最坏情况下才会出现对外表每一行 执行一次filter操作，
如果distinct值比较少，那执行效率还是非常高的，这个优化11g之前就有，虽然是filter操作，但是实际执行情况可能不是完全按照filter那样的逻辑执行
...

According to Note:5400635.8, if you have this hash anti join NA (null aware) run in parallel, you may get wrong result. But according to Bug 5699658 where wrong result is generated, "HASH JOIN ANTI NA" appears probably without running in parallel or with outer join. If that's the case, we should be careful when we see this line in execution plan and remember to check the result for correctness.

Can you explain a little more about "这个优化11g之前就有，虽然是filter操作..."? Thanks.

Yong Huang

zjuzsf

10g下做测试
SAMPLE
表t记录50207条，
distinct owner   24


[email=T@ORA]T@ORA>DESC[/email] T
Name                                                                   Null?    Type
---------------------------------------------------------------------- -------- -----------------------------------------------
OWNER                                                                           VARCHAR2(30)
OBJECT_NAME                                                            NOT NULL VARCHAR2(30)
SUBOBJECT_NAME                                                                  VARCHAR2(30)
OBJECT_ID                                                              NOT NULL NUMBER
DATA_OBJECT_ID                                                                  NUMBER
OBJECT_TYPE                                                                     VARCHAR2(19)
CREATED                                                                NOT NULL DATE
LAST_DDL_TIME                                                          NOT NULL DATE
TIMESTAMP                                                                       VARCHAR2(19)
STATUS                                                                          VARCHAR2(7)
TEMPORARY                                                                       VARCHAR2(1)
GENERATED                                                                       VARCHAR2(1)
SECONDARY                                                                       VARCHAR2(1)
[email=T@ORA]T@ORA>SELECT[/email] COUNT(*) FROM T;
  COUNT(*)
----------
     50207
Elapsed: 00:00:00.01
[email=T@ORA]T@ORA>select/*+gather_plan_statistics*/[/email] count(OWNER) from t WHERE OWNER NOT IN (SELECT/*+NO_UNNEST*/ OWNER FROM T);
COUNT(OWNER)
------------
           0
Elapsed: 00:00:00.57
[email=T@ORA]T@ORA>SELECT[/email] * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1xj0j83009vdz, child number 0
-------------------------------------
select/*+gather_plan_statistics*/ count(OWNER) from t WHERE OWNER NOT IN
(SELECT/*+NO_UNNEST*/ OWNER FROM T)
Plan hash value: 2615818063
--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.58 |   32895 |
|*  2 |   FILTER            |      |      1 |        |      0 |00:00:00.58 |   32895 |
|   3 |    TABLE ACCESS FULL| T    |      1 |  50207 |  50207 |00:00:00.15 |     694 |
|*  4 |    TABLE ACCESS FULL| T    |     59 |   2510 |     59 |00:00:00.53 |   32201 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( IS NULL)
   4 - filter(LNNVL("OWNER"<>:B1))

22 rows selected.
Elapsed: 00:00:00.31
按照正常的filter的操作，对计划行3中 返回的的每一行，都需要执行一次计划行4
而实际上上面的计划行4  |*  4 |    TABLE ACCESS FULL| T    |     59 |   
starts = 59，而不是 50207次（t中总记录数），最佳执行计划这里应该只执行24次（就是disinct owner的数），这个可以从执行计划的cost中可以可能出来，
oracle优化器在计算代价的时候就考虑了这个问题
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |  7602   (2)| 00:00:39 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T    | 50207 |   294K|   361   (2)| 00:00:02 |
|*  4 |    TABLE ACCESS FULL| T    |  2510 | 15060 |   362   (2)| 00:00:02 |
----------------------------------------------------------------------------

看cost列，行3 cost = 361 行4 cost = 362  
361+362*20 = 7602  
因为这里oracle认为distnct owner 数为20（这个是统计信息问题），所以filter里面的 table access full只需要执行20次，而不是50207次，代价计算也反映出这个优化，
实际执行的时候因为数据关系不是执行了20次，而是59次，但是比起50207次性能提高还是特别明显的

再看一个更明显的例子
CREATE TABLE EMP AS SELECT ROWNUM EMPNO,ROWNUM SAL,MOD(ROWNUM,6) DEPTNO FROM ALL_OBJECTS NOLOGGING;
distinct deptno  6

[email=T@ORA]T@ORA>select/*+gather_plan_statistics*/[/email] count(*) from emp o WHERE sal > (SELECT/*+NO_UNNEST*/ avg(sal) FROM emp where emp.deptno = o.deptno);
  COUNT(*)
----------
     25104
Elapsed: 00:00:00.20
[email=T@ORA]T@ORA>SELECT[/email] * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d9r5a705ff1zk, child number 0
-------------------------------------
select/*+gather_plan_statistics*/ count(*) from emp o WHERE sal >
(SELECT/*+NO_UNNEST*/ avg(sal) FROM emp where emp.deptno = o.deptno)
Plan hash value: 1040767149
---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:00.13 |     875 |
|*  2 |   FILTER             |      |      1 |        |  25104 |00:00:00.19 |     875 |
|   3 |    TABLE ACCESS FULL | EMP  |      1 |  50208 |  50208 |00:00:00.15 |     125 |
|   4 |    SORT AGGREGATE    |      |      6 |      1 |      6 |00:00:00.06 |     750 |
|*  5 |     TABLE ACCESS FULL| EMP  |      6 |   8368 |  50208 |00:00:00.15 |     750 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SAL">)
   5 - filter("EMP"."DEPTNO"=:B1)

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     8 |   512   (8)| 00:00:03 |
|   1 |  SORT AGGREGATE      |      |     1 |     8 |            |          |
|*  2 |   FILTER             |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL | EMP  | 50208 |   392K|    73   (7)| 00:00:01 |
|   4 |    SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  5 |     TABLE ACCESS FULL| EMP  |  8368 | 66944 |    73   (7)| 00:00:01 |
-----------------------------------------------------------------------------

cost 512    接近于           73 + 73*6

fusnow

当子查询中distinct较少的时候，oracle关于filter的优化在Cost Based Oracle Fundamentals里有介绍的，transform那一章

filter的大概意思Yong Huang 前面说得基本上差不多，这个东西常常出现在优化器无法对你的子查询做转化的时候
，如果出现了不能说性能一定不好，只不过性能不好的可能性比较大罢了。

至于楼主那个情况，可以看看子查询对应字段上是不是Not null，如果不是改成not null 看看

棉花糖ONE

SQL> create  table test(id int);

表已创建。

SQL> insert into test select rownum from dba_objects;

已创建49993行。

SQL> commit;

提交完成。

SQL> create table test1(id int);

表已创建。

SQL> insert into test1 select 1 from user_objects;

已创建23121行。

SQL>  insert into test1 select 2 from user_objects;

已创建23121行。

SQL> commit;

提交完成。

SQL> set autot trace
SQL> select * from test where id not in (select id from test1);

已选择49991行。


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3143 Card=49927 By
          tes=649051)

   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=19 Card=4995
          9 Bytes=649467)

   3    1     TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=2 Card=4526
          9 Bytes=588497)





Statistics
----------------------------------------------------------
         58  recursive calls
          0  db block gets
    3703065  consistent gets
          0  physical reads
          0  redo size
     703003  bytes sent via SQL*Net to client
      37156  bytes received via SQL*Net from client
       3334  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49991  rows processed

SQL> select * from test where not exists(select 1 from test1 where test.id=test1.id);

已选择49991行。


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=155 Card=49957 Byt
          es=1298882)

   1    0   HASH JOIN (RIGHT ANTI) (Cost=155 Card=49957 Bytes=1298882)
   2    1     TABLE ACCESS (FULL) OF 'TEST1' (TABLE) (Cost=18 Card=476
          51 Bytes=619463)

   3    1     TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=19 Card=4995
          9 Bytes=649467)





Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
       3622  consistent gets
          0  physical reads
          0  redo size
     703003  bytes sent via SQL*Net to client
      37156  bytes received via SQL*Net from client
       3334  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      49991  rows processed