索引对单表查询的影响（Cost和consistent gets）

转载

mob604756ef35df 2020-02-03 12:17:00

前提：使用system账户登录sql plus。

建表：

SQL> create table t2 as select * from dba_objects;

表已创建。

已用时间:  00: 00: 00.81
SQL> insert into t2 select * from t2;

已创建72695行。

已用时间:  00: 00: 00.98
SQL> insert into t2 select * from t2;

已创建145390行。

已用时间:  00: 00: 00.71
SQL> insert into t2 select * from t2;

已创建290780行。

已用时间:  00: 00: 02.40
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00

不加索引时查询：

SQL> select * from t2 where object_id=99;

已选择8行。

已用时间:  00: 00: 00.03

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    91 | 18837 |  2250   (1)| 00:00:27 |
|*  1 |  TABLE ACCESS FULL| T2   |    91 | 18837 |  2250   (1)| 00:00:27 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=99)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       8331  consistent gets
          0  physical reads
          0  redo size
       1774  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

添加索引后执行同样sql：

SQL> create index idx_t2_object_id on t2(object_id);

索引已创建。

已用时间:  00: 00: 00.73
          
          
SQL> select * from t2 where object_id=99;

已选择8行。

已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 188336643

--------------------------------------------------------------------------------
----------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%
CPU)| Time     |

--------------------------------------------------------------------------------
----------------

|   0 | SELECT STATEMENT            |                  |     8 |  1656 |    12
 (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2               |     8 |  1656 |    12
 (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T2_OBJECT_ID |     8 |       |     3
 (0)| 00:00:01 |

--------------------------------------------------------------------------------
----------------


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

   2 - access("OBJECT_ID"=99)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         90  consistent gets
          2  physical reads
          0  redo size
       2236  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed