order by 列加索引

create table t_order_tb asselect * from dba_objects ; select * from t_order_tb t where t.object_id > 2 ;select * from t_order_tb t where t.object_id > 2 order by t.object_id;create index ind_order_object_id on t_order_tb(object_id) ;select * from t_order_tb t where t.object_id > 2 order by t.object_id;select t.object_id from t_order_tb t where t.object_id > 2 order by t.object_id;SQL> set autotrace traceonly;SQL> select * from t_order_tb t where t.object_id > 2 ;已选择50628行。已用时间:  00: 00: 02.25执行计划----------------------------------------------------------Plan hash value: 2653850918--------------------------------------------------------------------------------| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |            | 41858 |  7235K|   162   (3)| 00:00:02 ||*  1 |  TABLE ACCESS FULL| T_ORDER_TB | 41858 |  7235K|   162   (3)| 00:00:02 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("T"."OBJECT_ID">2)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          4  recursive calls          0  db block gets       4080  consistent gets        399  physical reads          0  redo size    2453530  bytes sent via SQL*Net to client      37510  bytes received via SQL*Net from client       3377  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      50628  rows processedSQL>SQL> select * from t_order_tb t where t.object_id > 2 order by t.object_id;已选择50628行。已用时间:  00: 00: 01.50执行计划----------------------------------------------------------Plan hash value: 3469110092-----------------------------------------------------------------------------------------| Id  | Operation          | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |            | 41858 |  7235K|       |  1793   (1)| 00:00:22 ||   1 |  SORT ORDER BY     |            | 41858 |  7235K|    16M|  1793   (1)| 00:00:22 ||*  2 |   TABLE ACCESS FULL| T_ORDER_TB | 41858 |  7235K|       |   162   (3)| 00:00:02 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("T"."OBJECT_ID">2)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          4  recursive calls          0  db block gets        758  consistent gets          0  physical reads          0  redo size    2447912  bytes sent via SQL*Net to client      37510  bytes received via SQL*Net from client       3377  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)      50628  rows processedSQL> select * from t_order_tb t where t.object_id > 2 order by t.object_id;已选择50628行。已用时间:  00: 00: 01.68执行计划----------------------------------------------------------Plan hash value: 780952064---------------------------------------------------------------------------------------------------| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                     | 41858 |  7235K|   876   (1)| 00:00:11 ||   1 |  TABLE ACCESS BY INDEX ROWID| T_ORDER_TB          | 41858 |  7235K|   876   (1)| 00:00:11 ||*  2 |   INDEX RANGE SCAN          | IND_ORDER_OBJECT_ID | 41858 |       |   126   (2)| 00:00:02 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("T"."OBJECT_ID">2)       filter("T"."OBJECT_ID">2)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          9  recursive calls          0  db block gets       7609  consistent gets        112  physical reads          0  redo size    5439440  bytes sent via SQL*Net to client      37510  bytes received via SQL*Net from client       3377  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      50628  rows processedSQL>SQL> select t.object_id from t_order_tb t where t.object_id > 2 order by t.object_id;已选择50628行。已用时间:  00: 00: 00.48执行计划----------------------------------------------------------Plan hash value: 957369183----------------------------------------------------------------------------------------| Id  | Operation        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                     | 41858 |   531K|   126   (2)| 00:00:02 ||*  1 |  INDEX RANGE SCAN| IND_ORDER_OBJECT_ID | 41858 |   531K|   126   (2)| 00:00:02 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T"."OBJECT_ID">2)       filter("T"."OBJECT_ID">2)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          7  recursive calls          0  db block gets       3546  consistent gets          0  physical reads          0  redo size     732819  bytes sent via SQL*Net to client      37510  bytes received via SQL*Net from client       3377  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)      50628  rows processed