这个 filter ( IS NOT NULL)是什么意思？

Samdy_Chan · 发表于 2015-11-29 17:34

各位好：
请问在以下附图的执行计划中的 ID 为 1 的执行步骤 FILTER 对应的执行信息 1 - filter ( IS NOT NULL) 是什么意思呢？

lfree · 发表于 2015-11-29 19:35

我感觉是少了字段名.

2009532140 · 发表于 2015-11-30 08:44

lfree 发表于 2015-11-29 19:35
我感觉是少了字段名.

这里的filter 不是跟NL 很类似么？
只不过这里维护了hash 表

lfree · 发表于 2015-11-30 08:53

lz 把sql语句帖出来把。。

maolinxie · 发表于 2015-11-30 09:45

EMPNO是否有索引

Samdy_Chan · 发表于 2015-11-30 12:48

SQL如下：
04:58:26 SYS@orcl*SQL> select empno,ename from scott.emp where empno in (select /*+ no_unnest */ empno from emp_mgr_view);

            EMPNO ENAME
-------------------- --------------------
            7566 JONES
            7698 BLAKE
            7782 CLARK

执行计划
05:10:50 SYS@orcl*SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bv39y8w2ymdx7, child number 0
-------------------------------------
select empno,ename from scott.emp where empno in (select /*+ no_unnest
*/ empno from emp_mgr_view)

Plan hash value: 4147161479

-------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |             |    |    | 24 (100)|       |
|*  1 |  FILTER             |             |    |    |          |       |
| 2 | TABLE ACCESS FULL  | EMP       | 14 | 140 |    3 (0)| 00:00:01 |
|*  3 | VIEW             | EMP_MGR_VIEW |    3 | 39 |    3 (0)| 00:00:01 |
|*  4 | COUNT STOPKEY    |             |    |    |          |       |
|*  5 |    TABLE ACCESS FULL| EMP_TEMP    |    3 | 36 |    3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
......省略部分......

Outline Data
-------------
......省略部分......

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( IS NOT NULL)
3 - filter("EMPNO"=:B1)
4 - filter(ROWNUM<10)
5 - filter("JOB"='MANAGER')

lfree · 发表于 2015-11-30 14:49

select empno,ename from scott.emp where empno in (select /*+ no_unnest */ empno from emp_mgr_view);

emp_mgr_view 的定义吗？

lfree · 发表于 2015-12-8 07:47

[20151207]filter( IS NULL).txt

--前一阵子别人问的问题，filter (IS NOT NULL)是什么意思？
-- http://www.itpub.net/thread-1943880-1-1.html

--正好看刘工的视频"51CTO学院-Oracle性能优化精讲视频课程【刘相兵】-ORACLE Maclean 的Oracle性能优化讲座",找到如下
--语句，通过它来说明？

SCOTT@book> @ &r/ver1
PORT_STRING                   VERSION       BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx          11.2.0.4.0    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> alter session set statistics_level=all;
Session altered.

SELECT /*+ RULE to make sure it reproduces 100% */
   ename
   ,job
   ,sal
   ,dname
  FROM emp, dept
WHERE    dept.deptno = emp.deptno
   AND NOT EXISTS
            (SELECT *
               FROM salgrade
            WHERE emp.sal BETWEEN losal AND hisal);

SCOTT@book> @ &r/dpcz
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  anbbf4bvm5n5n, child number 0
-------------------------------------
SELECT /*+ RULE to make sure it reproduces 100% */    ename
,job    ,sal    ,dname FROM emp, dept  WHERE    dept.deptno =
emp.deptno       AND NOT EXISTS             (SELECT *
  FROM salgrade                WHERE emp.sal BETWEEN losal AND hisal)
Plan hash value: 243245009
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |       |    1 |    0 |00:00:00.01 |    109 |
|*  1 |  FILTER                      |       |    1 |    0 |00:00:00.01 |    109 |
| 2 | NESTED LOOPS             |       |    1 |    14 |00:00:00.01 |    25 |
| 3 | NESTED LOOPS             |       |    1 |    14 |00:00:00.01 |    11 |
| 4 |    TABLE ACCESS FULL       | EMP    |    1 |    14 |00:00:00.01 |    7 |
|*  5 |    INDEX UNIQUE SCAN       | PK_DEPT  |    14 |    14 |00:00:00.01 |    4 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT    |    14 |    14 |00:00:00.01 |    14 |
|*  7 | TABLE ACCESS FULL          | SALGRADE |    12 |    12 |00:00:00.01 |    84 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / EMP@SEL$1
5 - SEL$1 / DEPT@SEL$1
6 - SEL$1 / DEPT@SEL$1
7 - SEL$2 / SALGRADE@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
7 - filter(("HISAL">=:B1 AND "LOSAL"<=:B2))
Note
-----
- rule based optimizer used (consider using cbo)

--这里的fliter ID＝1条件是1 - filter( IS NULL).如果理解这个条件，就很好理解filter( IS NOT NULL)

SELECT /*+ RULE to make sure it reproduces 100% */
   ename
   ,job
   ,sal
   ,dname
  FROM emp, dept
WHERE    dept.deptno = emp.deptno
   AND EXISTS
            (SELECT *
               FROM salgrade
            WHERE emp.sal BETWEEN losal AND hisal);