order by 列加索引

create table t_order_tb 
as
select * 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 processed

SQL>
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 processed

SQL> 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 processed

SQL>
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