Filter 很低效。
  比如这种SQL
SELECT * FROM test1 a WHERE a.c1 NOT IN (SELECT c1 FROM test2)
  计划就是

  filter
     --full
     --full

  两表都10w左右，无索引，平均行长90byte，天文数字一样的 consistent get。

难道到是做descartes再过滤，那么是在PGA （dedicate），SGA（mts）里面做的descartes ？？

请高手指导一下。

You can't blame filter. Oracle has no choice but do full scan on the tables. Why not create an index?

Filter simply means the process has already fetched the rows but needs to filter out those that don't meet the where clause and return the rest. In contrast, an access path means it takes the possibly cost-saving path to get the rows.

Yong Huang

Thanks.

  But i want know the mechanism of this operation,is it make descartes firstly?
  Can you give more info about the infrastructure?

Filter operation causes Cartesian joins? Is that what you ask? No it does not. I'm not sure where you can find more info. Maybe the Performance Tuning manual in documentation. If not, search on Metalink.

Anybody else has more info about filters, access paths, etc? Not general documents, but focus on these operations.

Yong Huang

这种not in的查询应该是笛卡尔连接，
大致应该是：
先把两张表的数据块都读入sga，
然后表test1的每一个记录去比对表test2中的所有记录，
一致读肯定是n×m，
这个跟nest loop还不一样，NL是满足条件后就返回结果，
而not in操作决定了，必须是在扫描所有记录后，才能返回结果。
不管是dedicate还是mts，这些操作都应该是在sga中，
只有发生sort或者hash join时，才会在pga中进行。

个人理解。

个人认为Filter是什么操作，关键还要看连接方式，
像这种not in的连接肯定是笛卡尔，
理想情况应该是NL，满足条件了就返回结果

你可以用anti join，可以大大提高速度

SELECT * FROM test1 a WHERE a.c1 NOT IN (SELECT /*+ hash_aj */ c1 FROM test2)

但是有一个条件就是你的c1列是非空的，或者你可以用is not null来限定

看看我的试验：

PHP code:



SQL> SELECT * FROM t1 a WHERE a.object_id NOT IN (SELECT object_id FROM t2 b)


  2  ;





no rows selected








Execution Plan


----------------------------------------------------------


Plan hash value: 3597920939





-----------------------------------


| Id  | Operation          | Name |


-----------------------------------


|   0 | SELECT STATEMENT   |      |


|*  1 |  FILTER            |      |


|   2 |   TABLE ACCESS FULL| T1   |


|*  3 |   TABLE ACCESS FULL| T2   |


-----------------------------------





Predicate Information (identified by operation id):


---------------------------------------------------





   1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "B" WHERE


              LNNVL(&quot;OBJECT_ID&quot;<>:B1)))


   3 - filter(LNNVL(&quot;OBJECT_ID&quot;<>:B1))





Note


-----


   - rule based optimizer used (consider using cbo)








Statistics


----------------------------------------------------------


          1  recursive calls


          0  db block gets


    1288020  consistent gets


          0  physical reads


          0  redo size


       1139  bytes sent via SQL*Net to client


        458  bytes received via SQL*Net from client


          1  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          0  rows processed





SQL> alter table t1 modify object_id not null;





Table altered.





SQL> alter table t2 modify object_id not null;





Table altered.








SQL> SELECT * FROM t1 WHERE object_id not in (SELECT /*+ hash_aj */ object_id FROM t2);





no rows selected








Execution Plan


----------------------------------------------------------


Plan hash value: 3791526412





-----------------------------------------------------------


| Id  | Operation          | Name | Rows  | Bytes | Cost  |


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |      |     1 |    83 |    71 |


|*  1 |  HASH JOIN ANTI    |      |     1 |    83 |    71 |


|   2 |   TABLE ACCESS FULL| T1   | 19882 |  1533K|    25 |


|   3 |   TABLE ACCESS FULL| T2   | 19882 | 79528 |    25 |


-----------------------------------------------------------





Predicate Information (identified by operation id):


---------------------------------------------------





   1 - access(&quot;OBJECT_ID&quot;=&quot;OBJECT_ID&quot;)





Note


-----


   - cpu costing is off (consider enabling it)








Statistics


----------------------------------------------------------


          1  recursive calls


          0  db block gets


        512  consistent gets


          0  physical reads


          0  redo size


       1139  bytes sent via SQL*Net to client


        458  bytes received via SQL*Net from client


          1  SQL*Net roundtrips to/from client


          0  sorts (memory)


          0  sorts (disk)


          0  row

[ 本帖最后由 eagle_fan 于 2008-4-4 17:48 编辑 ]

Filter
  A
  B

就是对于A上的每一行，filter B中的数据。

举例来说明

PHP code:



SQL> explain plan for SELECT * FROM t1 a WHERE a.object_id NOT IN (SELECT object_id FROM t2 b)


  2  ;





Explained.





SQL> @?/rdbms/admin/utlxpls





PLAN_TABLE_OUTPUT


--------------------------------------------------------------------------------------------------------------------------------------------


Plan hash value: 3597920939





-----------------------------------------------------------


| Id  | Operation          | Name | Rows  | Bytes | Cost  |


-----------------------------------------------------------


|   0 | SELECT STATEMENT   |      |   994 | 78526 |    25 |


|*  1 |  FILTER            |      |       |       |       |


|   2 |   TABLE ACCESS FULL| T1   |   994 | 78526 |    25 |


|*  3 |   TABLE ACCESS FULL| T2   |     2 |     8 |    25 |


-----------------------------------------------------------





Predicate Information (identified by operation id):


---------------------------------------------------





   1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "B" WHERE


              "OBJECT_ID"=:B1))


   3 - filter("OBJECT_ID"=:B1)

对于T1中的每一行，到T2中找相应的行，看是否存在T2.object_id = T1.object_id，如果有的话就立刻丢弃，如果没有的话符合条件。

[ 本帖最后由 eagle_fan 于 2008-4-4 17:59 编辑 ]

filter 真是恐怖的家伙

在11g里面 not in有了比较大变化，以前版本如果返回列可以为空不能使用anti join，在11g里面可以为null的也能使用anti join了
T@ORCL>DESC TT
Name                                                                   Null?    Type
---------------------------------------------------------------------- -------- ----------------------------------------
OWNER                                                                           VARCHAR2(30)
OBJECT_NAME                                                                     VARCHAR2(128)
SUBOBJECT_NAME                                                                  VARCHAR2(30)
OBJECT_ID                                                                       NUMBER
DATA_OBJECT_ID                                                                  NUMBER
OBJECT_TYPE                                                                     VARCHAR2(19)
CREATED                                                                         DATE
LAST_DDL_TIME                                                                   DATE
TIMESTAMP                                                                       VARCHAR2(19)
STATUS                                                                          VARCHAR2(7)
TEMPORARY                                                                       VARCHAR2(1)
GENERATED                                                                       VARCHAR2(1)
SECONDARY                                                                       VARCHAR2(1)
NAMESPACE                                                                       NUMBER
EDITION_NAME                                                                    VARCHAR2(30)

T@ORCL>select count(*) from tt WHERE OBJECT_ID NOT IN (SELECT OBJECT_ID FROM TT);

  COUNT(*)
----------
         0

Elapsed: 00:00:00.12

[email=T@ORCL]T@ORCL>select[/email] 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那样的逻辑执行


[email=T@ora]T@ora>select[/email] count(*) from t;
  COUNT(*)
----------
    100412
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   715   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 91878 |   715   (2)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1446  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
[email=T@ora]T@ora>select[/email] count(*) from t WHERE SUBOBJECT_NAME NOT IN (SELECT  SUBOBJECT_NAME FROM T);
  COUNT(*)
----------
         0
Elapsed: 00:00:00.25
Execution Plan
----------------------------------------------------------
Plan hash value: 2615818063
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    17 |  2061K  (2)| 02:51:49 |
|   1 |  SORT AGGREGATE     |      |     1 |    17 |            |          |
|*  2 |   FILTER            |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T    | 91878 |  1525K|   717   (2)| 00:00:04 |
|*  4 |    TABLE ACCESS FULL| T    | 87284 |  1449K|   718   (2)| 00:00:04 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T" "T" WHERE
              LNNVL("SUBOBJECT_NAME"<>:B1)))
   4 - filter(LNNVL("SUBOBJECT_NAME"<>:B1))
Note
-----
   - dynamic sampling used for this statement

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