189 8069 5689
--索引与排序
创新互联建站专业为企业提供东港网站建设、东港做网站、东港网站设计、东港网站制作等企业网站建设、网页设计与制作、东港企业网站模板建站服务，十多年东港做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
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;