本帖最后由 dotaddjj 于 2014-3-21 18:07 编辑
INDEX FULL SCAN DESCENDING和index_range_scan的区别,没加order by时,毫无疑问inde range scan是最合适的,加order by后,而后对另一个列排序,此时index range scan返回的数据在cbo看来是无需的,回表后还需要sort by,cbo考虑成本直接选择了index full scan来扫描,而这个会扫描所有的索引建值然后回表,感觉这里cbo的选择并不合适,索引是单块读,而且由于回表的数据物理块不连续,会带来较多的随机读和重复读,收集下统计信息看看,cbo是否会改变成先index range scan然后回表,最后sort排序!
也可以修改业务逻辑,把索引的前导列写上来避免index range scan回表后还进行sort排序的消耗!
SQL> create index ind_table_mulit on table01(object_id,created);
索引已创建。
SQL> select * from table01 where object_id<10000 order by created;
已选择9707行。
执行计划
----------------------------------------------------------
Plan hash value: 69786109
--------------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 9758 | 952K| |
393 (1)| 00:00:05 |
| 1 | SORT ORDER BY | | 9758 | 952K| 1312K|
393 (1)| 00:00:05 |
| 2 | TABLE ACCESS BY INDEX ROWID| TABLE01 | 9758 | 952K| |
170 (1)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | INDEX_TABLE_ID | 9758 | | |
23 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID"<10000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
161 consistent gets
0 physical reads
0 redo size
475525 bytes sent via SQL*Net to client
7640 bytes received via SQL*Net from client
649 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9707 rows processed
SQL> select * from table01 where object_id<10000 order by object_id,created;
已选择9707行。
执行计划
----------------------------------------------------------
Plan hash value: 400278357
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 9758 | 952K| 181
(1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE01 | 9758 | 952K| 181
(1)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_TABLE_MULIT | 9758 | | 34
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1453 consistent gets
28 physical reads
0 redo size
1020151 bytes sent via SQL*Net to client
7640 bytes received via SQL*Net from client
649 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9707 rows processed