Access and filter

Access: The value that represents this predicate condition will affect the access path (table or index) of the data.

Filter : The value that represents the predicate condition does not affect the data access path, it only acts as a filter.

----Create a table echo----sql> CREATE TABLE echo as select * from Dba_objects; Table created. sql> set Autotrace trace exp; Sql> set linesize 150; Sql> SELECT * from Echo where object_id=1000; Execution Plan----------------------------------------------------------plan hash value:642657756---------------- ----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |--------------------------------------------------------------------------| 0 |     SELECT STATEMENT |    |  12 |   2484 | 289 (1) | 00:00:04 | |  * 1 | TABLE ACCESS full|    ECHO |  12 |   2484 | 289 (1) | 00:00:04 |--------------------------------------------------------------------------predicate information ( identified by Operation ID):---------------------------------------------------1-filter ("object_id" =1000)---- Because the table echo does not create an index, the execution plan does not have the option of selecting a data access path, and the predicate condition here is just a function of data filtering, so filter is used. Note------Dynamic sampling used for this statement (level=2)----When creating an index----sql> CREATE INDEX Echo_ind on Echo (object_id); index created. Sql> SELECT * from Echo where object_id=1000; Execution Plan----------------------------------------------------------plan hash value:1345159126--------------- -------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |   Time |----------------------------------------------------------------------------------------| 0 |           SELECT STATEMENT |     |   1 |     207 | 2 (0) |   00:00:01 | |  1 | TABLE ACCESS by INDEX rowid|     ECHO |   1 |     207 | 2 (0) | 00:00:01 | |   * 2 | INDEX RANGE SCAN |     Echo_ind |       1 |     | 1 (0) | 00:00:01 |----------------------------------------------------------------------------------------predicate Information (identified by Operation ID):---------------------------------------------------2-access ("object_id" = ----predicate conditions affect the path to the data access and the index is selected, so access。 Note------Dynamic sampling used for this statement (level=2)

The above is transferred from the network

You should be careful about the predicate information about access and think of yourself as an optimizer, to understand why the predicate condition affects the access path in this way.

No index there is no choice, the predicate condition must not feel the access path, it can only be filter. For filter, only one of the following nodes is generally just a filter, but the extra two

Nodes are basically involved in loops that are driven by a driver table, and can generally be avoided by overwriting SQL.