建立测试用表
create table t as select * from dba_objects;
create index idx_t_objectid on t(object_id);
查看执行计划
select * from t where object_id<10;
ZBB@test>select * from t where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3530610702
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1656 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 8 | 1656 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJECTID | 8 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1921 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
ZBB@test>
修改语句,修改显示查询列为必要的列,
select object_id from t where object_id<10;
ZBB@test>select object_id from t where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1876860048
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 104 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T_OBJECTID | 8 | 104 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
629 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
ZBB@test>
通过以上的对比, 使用count(*)的时候,cost为3,逻辑读为5。仅仅查询object_id的时候,cost为2,逻辑读为3.
所以在查询的时候,避免不必要的列,还是很重要的。
-- 如果需要查询object_id,object_name列,但是只有object_id有索引,怎样避免回表?可以考虑使用object_id,object_name的组合列索引。
-- 查看不使用组合列索引的执行计划
select object_id,object_name from t where object_id<10;
ZBB@test>select object_id,object_name from t where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3530610702
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 8 | 632 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJECTID | 8 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
768 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
ZBB@test>
创建一个组合索引,查看执行计划,发现没有回表了 . cost从3变成了2,逻辑读从5变成了3 .
create index idx_object_id_name on t(object_id,object_name);
select object_id,object_name from t where object_id<10;
ZBB@test>select object_id,object_name from t where object_id<10;
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1967165601
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1106 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_OBJECT_ID_NAME | 14 | 1106 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"<10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
768 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
ZBB@test>
END