索引系列十--索引特性之有序优化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 traceonly

set linesize 1000

drop 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;