Explore the filter principles in execution plans

I have written an article about the performance problems caused by filter, which is written at the beginning:

The word "filter" is confusing. a subnode, two subnodes, and three subnodes can be mounted at the next level ....

Apply a subnode to filter data. For example, after a full table is scanned, the data that does not meet the conditions is discarded.

Hanging two subnodes is similar to nest loop.

The three subnodes are like 1 and 2 for nest loop, and the result set is done with 3 for nest loop (I guess ).

In order to prove our current point of view, we will conduct an experiment below:
Put a node under the filter:
When a node is attached, the following example shows that a table is scanned once to filter the returned result set. However, it is interesting that when the condition is false, the table is not scanned.
SQL> select * from e where e. hiredate> sysdate-10000 and e. hiredate <sysdate;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------
7788 scott analyst 7566-87 3000 20
7876 adams clerk 7788 month-87 1100 20
SQL> alter session set statistics_level = all;
The session has been changed.
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Bytes ------------------------------------------------------------------------------------------------------
SQL _ID 982y5g2z9sn6f, child number 0
-------------------------------------
Select * from emp e where e. hiredate> sysdate-10000 and e. hiredate <
Sysdate
Plan hash value: 3896240783
Bytes -------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes -------------------------------------------------------------------------------------
| * 1 | FILTER | 1 | 2 | 00:00:00. 01 | 8 |
| * 2 | table access full | EMP | 1 | 2 | 2 | 00:00:00. 01 | 8 |
Bytes -------------------------------------------------------------------------------------
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;
Unselected row
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Bytes ----------------------------------------------------------------------------------------------------
SQL _ID 0pywmdg63gf0m, child number 0
-------------------------------------
Select * from e where e. hiredate> sysdate-10000 and e. hiredate
<Sysdate-10000
Plan hash value: 3896240783
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-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 ))

For more details, please continue to read the highlights on the next page:

1 2 Next Page