探索執行計畫中filter的原理

之前寫過一篇文章，filter造成的效能問題，開頭是這麼寫的： 

filter這個詞總讓人很費解，它下一級可以掛 一個子節點，二個子節點，三個子節點...。

掛一個子節點意思過濾，如對全表進行掃描後，按照條件過濾，丟棄不滿足條件的資料。

掛二個子節點類似是nest loop。

掛三個子節點類似1和2做nest loop，結果集再與3做nest loop（這個是我推測的）。

為了證明當時的觀點，我們下面來做個實驗：
filter下掛一個節點：
掛一個節點時，下面的例子可以看到，對錶掃描一次，就是對返回的結果集進行過濾，不過有趣的是當條件為假時，不會對錶進行掃描。
SQL> select * from emp e where e.hiredate > sysdate - 10000 and e.hiredate < sysdate;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
SQL> alter session set statistics_level=all;
會話已更改。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID 982y5g2z9sn6f, child number 0
-------------------------------------
select * from emp e where e.hiredate > sysdate - 10000 and e.hiredate <
sysdate
Plan hash value: 3896240783
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 2 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 2 | 2 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYSDATE@!-10000<SYSDATE@!)
2 - filter(("E"."HIREDATE">SYSDATE@!-10000 AND "E"."HIREDATE"<SYSDATE@!))

SQL> select * from emp e where e.hiredate > sysdate - 10000 and e.hiredate < sysdate - 10000;
未選定行
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 0pywmdg63gf0m, child number 0
-------------------------------------
select * from emp e where e.hiredate > sysdate - 10000 and e.hiredate
< sysdate - 10000
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL| EMP | 0 | 1 | 0 |00:00:00.01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYSDATE@!-10000<SYSDATE@!-10000)
2 - filter(("E"."HIREDATE">SYSDATE@!-10000 AND
"E"."HIREDATE"<SYSDATE@!-10000))

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁