SQL语句中的ACCESS和FILTER

最新推荐文章于 2024-04-28 18:28:03 发布

Flexes

最新推荐文章于 2024-04-28 18:28:03 发布

阅读量2.1k

点赞数

分类专栏： SQL 文章标签： SQL PLSQL

本文链接：https://blog.csdn.net/flexes/article/details/9885221

版权

SQL 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

Prediceate(谓词)：一个查询中的WHERE限制条件。

在查看执行计划的信息中，存在两个谓词filter和access

简单地说，执行计划如果显示是access，就表示这个谓词条件的值将会影响数据的访问路径(表还是索引)，

而filter表示谓词条件的值并不会影响数据访问路径，只起到过滤的作用。

SQL> create table echo as select * from dba_objects;

Table created.

SQL> select * from echo where object_id=1000;
SYS                            V_$BUFFER_POOL_STATISTICS
      1000                VIEW                30-JUN-05 30-JUN-05 2005-06-30:19:10:45 VALID   N N N

Execution Plan
----------------------------------------------------------
Plan hash value: 642657756

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     8 | 1416 |   160   (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| ECHO |     8 | 1416 |   160   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1000) ---无索引的情况下，只是单纯的过滤数据。

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        288 recursive calls
          0 db block gets
        804 consistent gets
        692 physical reads
          0 redo size
       1214 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

创建索引

SQL> create index echo_ind on echo(object_id);

Index created.

SQL> select * from echo where object_id=1000;
SYS                            V_$BUFFER_POOL_STATISTICS
      1000                VIEW                30-JUN-05 30-JUN-05 2005-06-30:19:10:45 VALID   N N N

Execution Plan
----------------------------------------------------------
Plan hash value: 1345159126

----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   177 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| ECHO     |     1 |   177 |     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"=1000)---谓词条件影响到数据访问的路径，选择了索引，所以用access
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9 recursive calls
          0 db block gets
         85 consistent gets
          4 physical reads
          0 redo size
       1218 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>