Access and filter in the execution plan prediceate

In view of the execution plan information, you will often see two predicates filter and access, what the difference is, understanding this two words will help us to interpret Oracle's execution plan information.

Simply put, the execution plan, if displayed as access, indicates that the value of this predicate condition will affect the access path (table or index) of the data, while the filter indicates that the value of the predicate condition does not affect the data access path, but only the filtering effect.

Access: In relation to how the table is accessed, access identifies how the table is accessed by index.
Filter: Only the role of filtering.
Seeing a few explain today has changed my understanding of this. The above understanding is also right, but incomplete,
I understand a little stiff.
Sql> CREATE TABLE T
2 as Select RowNum r,object_name
3 from Dba_objects
4/
Table created.
Sql> CREATE index T_idx on T (R);
Index created.
Sql> Execute dbms_stats.gather_table_stats (user, ' t ', cascade=>true)
PL/SQL procedure successfully completed.
Sql> Set Autotrace traceonly explain
Sql> SELECT * FROM t
2 where r = 10000;
Execution Plan
----------------------------------------------------------
Plan Hash value:470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |     |    1 |     30 | 2 (0) | 00:00:01 |
|  1 | TABLE ACCESS by INDEX rowid|     T |    1 |     30 | 2 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN |     T_idx |       1 |     | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-access ("R" =10000)

The predicate used is access, which accesses the index and then extracts the select result directly from the ROWID.
Sql> SELECT * FROM t
2 where R > 10000 and R < 50000
3/
Execution Plan
----------------------------------------------------------
Plan Hash value:1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------
| 0 |      SELECT STATEMENT | |  40001 |    1171k| 88 (2) | 00:00:02 |
|* 1 | TABLE ACCESS full| T |  40001 |    1171k| 88 (2) | 00:00:02 |
--------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-filter ("R" <50000 and "R" >10000)
The predicate used is that filter uses a full table scan, filtering out unwanted rows.
Sql> Select R from t
2 where R > 10000
3/
Execution Plan
----------------------------------------------------------
Plan Hash value:3163761342
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------
| 0 |       SELECT STATEMENT | |   55631 |    271k| 42 (3) | 00:00:01 |
|* 1 | INDEX FAST Full scan| T_idx |   55631 |    271k| 42 (3) | 00:00:01 |
------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-filter ("R" >10000)
The execution plan here is a bit of a point, using an index scan (indexed fast full scan),
However, it is not indicated by access. Oracle has decided to use an index scan, not necessarily through
Access to tell us. Here r can completely read the index to get the required column values, and
Most of the keys in the index need to be retrieved, so Oracle decides to use the index fast full scan, which
The way the index is accessed will read the bocks of the index by Multiblocks Reading, returning the result set
is unordered, and because it reads the index blocks, it needs to index blocks
Filter by the index keys in the.
Sql> CREATE TABLE EMP
2 AS Select Employee_id,first_name,last_name
3 from Hr.employees;
Table created.
Sql> CREATE INDEX EMP_IDX on EMP (employee_id,last_name);
Index created.
sql> exec dbms_stats.gather_table_stats (user, ' emp ', cascade=>true)
PL/SQL procedure successfully completed.
Sql> Select Employee_id,last_name
2 from EMP
3 where employee_id < last_name = ' King '
4/
Execution Plan
----------------------------------------------------------
Plan Hash value:3087982339
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 |         SELECT STATEMENT |     |    2 |     24 | 1 (0) | 00:00:01 |
|* 1 | INDEX RANGE scan|     Emp_idx |    2 |     24 | 1 (0) | 00:00:01 |
----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-access ("last_name" = ' King ' and "employee_id" <200)
Filter ("last_name" = ' King ')
My example above is also interesting, we created a composite index in front of it, and in the WHERE clause
The columns in the index are used. Oracle will access columns in the composite index based on where conditions employee_id < 200
If satisfied then filter out last_name = ' King ' index Key according to the condition filter.
Summary: Through the above examples, although the example is not very classic, but I think it can be explained.
1. If Oracle decides to use index to get a result set, you do not need to use the access verb to tell us that I (Oracle) used index.
2, access to data through index, it may also be necessary to use the filter.

Access and filter in the execution plan prediceate