[20151207]filter( IS NULL).txt

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