filter 与 access 的区别
filter 是过滤数据，access 是选择表的访问路径。
SQL> create table t1 (x int,y int);
表已创建。
SQL> set autotrace trace exp;
SQL> select /*+ rule */ * from t1 where x=5;
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T1 |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=5) --表t1 没有建立索引，此时不可能走索引，filter起到过滤数据的作用。
Note
-----
- rule based optimizer used (consider using cbo)
SQL> select * from t1 where x=5;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=5)
Note
-----
- dynamic sampling used for this statement
已用时间: 00: 00: 12.01
SQL> create table t1 ( x int , y int);
表已创建。
已用时间: 00: 00: 00.53
SQL> set autot trace exp
SQL> select * from t1 where x =5;
已用时间: 00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=5)
Note
-----
- dynamic sampling used for this statement
SQL> create index idx_t on t1(x,y);
索引已创建。
SQL> select * from t1 where x =5;
执行计划
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
1 - filter("X"=5)
Note
-----
- dynamic sampling used for this statement
SQL> select /*+ rule */ * from t1 where x=5;
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 2296882198
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| IDX_T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=5) --这次谓词影响到数据的访问路径选择索引。
Note
-----
- rule based optimizer used (consider using cbo)