请问Filter 是一种什么样的操作，它的机制是什么？ - Oracle专题深入讨论 - ITPUB论坛

Yong Huang
版主

精华贴数 2
个人空间 0
技术积分 3994 (347)
社区积分 120 (3013)
注册日期 2001-10-9
论坛徽章:6

#11

使用道具

发表于 2008-5-8 02:40

QUOTE:

原帖由 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
初级会员

精华贴数 0
个人空间 0
技术积分 117 (14440)
社区积分 0 (101862)
注册日期 2003-12-20
论坛徽章:0

#12

使用道具

发表于 2008-5-8 10:02

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

__________________

Oracle fans
PostgreSQL backend source code

只看该作者

fusnow
愚钝不开窍入门初级用户

精华贴数 0
个人空间 0
技术积分 6228 (204)
社区积分 2101 (554)
注册日期 2002-11-14
论坛徽章:10

#13

使用道具

发表于 2008-5-8 13:54

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

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

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

__________________

眼高手低的典型代表
我就是榆树临风，疯光旖旎，你死我活，活着要吃饭，饭可以乱吃话不可以乱说，说了也白说的无名老辈fusnow

只看该作者

棉花糖ONE

精华贴数 0
个人空间 0
技术积分 14353 (74)
社区积分 1233 (795)
注册日期 2007-2-21
论坛徽章:44

#14

使用道具

发表于 2008-5-23 13:47

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

__________________

慢和等待都是现象,不是原因,不能把现象当原因

只看该作者