索引系列十–索引特性之有序优化order by

–索引与排序drop table t purge;create table t as select * from dba_objects ;set autotrace traceonly–oracle还算智能，不会傻到这里都去排序，做了查询转换，忽略了这个排序select count(*) from t order by object_id;

—以下语句说明排序set autotrace traceonlyset linesize 1000drop table t purge;create table t as select * from dba_objects;
–以下语句没有索引又有order by ，必然产生排序select * from t where object_id>2 order by object_id;执行计划———————————————————————————–| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |———————————————————————————–| 0 | SELECT STATEMENT | | 92407 | 18M| | 4454 (1)| 00:00:54 || 1 | SORT ORDER BY | | 92407 | 18M| 21M| 4454 (1)| 00:00:54 ||* 2 | TABLE ACCESS FULL| T | 92407 | 18M| | 294 (2)| 00:00:04 |———————————————————————————–统计信息———————————————————- 0 recursive calls 0 db block gets 1047 consistent gets 0 physical reads 0 redo size 3513923 bytes sent via SQL*Net to client 54029 bytes received via SQL*Net from client 4876 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 73117 rows processed

—新增索引后，Oracle就有可能利用索引本身就有序的特点，利用索引来避免排序，如下：create index idx_t_object_id on t(object_id);set autotrace traceonly
select * from t where object_id>2 order by object_id;执行计划———————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |———————————————————————————————–| 0 | SELECT STATEMENT | | 92407 | 18M| 1302 (1)| 00:00:16 || 1 | TABLE ACCESS BY INDEX ROWID| T | 92407 | 18M| 1302 (1)| 00:00:16 ||* 2 | INDEX RANGE SCAN | IDX_T_OBJECT_ID | 92407 | | 177 (1)| 00:00:03 |———————————————————————————————–统计信息———————————————————- 0 recursive calls 0 db block gets 10952 consistent gets 0 physical reads 0 redo size 8115221 bytes sent via SQL*Net to client 54029 bytes received via SQL*Net from client 4876 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73117 rows processed
–如下情况Oracle肯定毫不犹豫的选择用索引,因为回表取消了 ! select object_id from t where object_id>2 order by object_id;执行计划————————————————————————————| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |————————————————————————————| 0 | SELECT STATEMENT | | 92407 | 1173K| 177 (1)| 00:00:03 ||* 1 | INDEX RANGE SCAN| IDX_T_OBJECT_ID | 92407 | 1173K| 177 (1)| 00:00:03 |————————————————————————————统计信息——————————————————–免费云主机域名— 0 recursive calls 0 db block gets 5027 consistent gets 0 physical reads 0 redo size 1062289 bytes sent via SQL*Net to client 54029 bytes received via SQL*Net from client 4876 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73117 rows processed –另外，如果是如下语句，Oracle打死也不用索引了。 select object_id from t where object_id>2;

相关推荐: Oracle DB 相关常用sql汇总7【手工绑定sql执行计划】

SPO coe_xfr_sql_profile.log; SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIM…