[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);
