Access and filter, accessfilter

Access and filter, accessfilter

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

Filter: indicates that the value of the predicate condition does not affect the data access path and only filters data.

---- 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 (% CP U) | Time | ------------------------------------------------------------------------------ | 0 | select statement | 12 | 2484 | 289 (1) | 00:00:04 | * 1 | table access full | ECHO | 12 | 2484 | 289 (1) | 00:00:04 | identified Predicate Information (identified by operation id ): --------------------------------------------------- 1-filter ("OBJEC T_ID "= 1000) ---- because the table echo does not create an index, there is no room to select the data access path for the execution plan. The predicate condition is used only for data filtering, so the filter is used. Note ------dynamic sampling used for this statement (level = 2) ---- create 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 bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 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 | predict Predicate In Formation (identified by operation id): ------------------------------------------------- 2-access ("OBJECT_ID" = 1000) ---- the predicate condition affects the data access path. The index is selected, so access is used. Note ------dynamic sampling used for this statement (level = 2)

Above