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