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)

【提示】filter 與access 的區別

相關文章