Oracle_ Execution Plan _ predicate information and data acquisition (access and filter differences) (GO)

These terms in the predicate information sections indicate when the data source is reduced. Simply, access means only retrieve those records meeting the condition and ignore others. Filter means *after* already got the data, go through them all and keep those meeting the condition and throw away the Others.

Access: Get the data that meets the criteria directly, discarding other unsatisfied data
Filter: You already have some data to apply filter to the existing data to get the data that satisfies the filter.

http://www.itpub.net/forum.php?mod=viewthread&tid=1766289

One: Brief description

In viewing the execution plan information, you will often see two predicates filter and access, what are the differences, and understanding the two words will help us to interpret Oracle's execution plan information.

Simply put, the execution plan if the display is access, the value of this predicate condition will affect the access path (table or index) of the data, and filter means that the value of the predicate condition does not affect the data access path, only the role of filtering.

II: Examples
Sql> CREATE table zhou_t (x int, y int);
The table is created.
sql> set Autotrace trace exp;
Sql> Select/*+rule*/* from zhou_t where x=5;
Execution plan
----------------------------------------------------------
Plan Hash value:1395150869
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS full| zhou_t |
------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-filter ("X" =5)
Note
-----
-rule based optimizer used (consider using CBO)

Because the table zhou_t does not create an index, the execution plan does not have the option of choosing a data access path, where the predicate condition is only used for data filtering, so the filter

What if an index is created on the table?

Sql> CREATE index Zhou_t_idx on zhou_t (x, y);
The index has been created.
Sql> Select/*+rule*/* from zhou_t where x=5;
Execution plan
----------------------------------------------------------
Plan Hash value:42197324
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE scan| Zhou_t_idx |
---------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-access ("X" =5)
Note
-----
-rule based optimizer used (consider using CBO)

As you can see from the above, the predicate condition affects the path to the data access------The index is selected, so access

http://zuoren110.blog.163.com/blog/static/617563201201331427675/

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.

http://blog.csdn.net/kkdelta/article/details/7938653

Oracle_ Execution Plan _ predicate information and data acquisition (access and filter differences) (GO)