sql中order by与索引排序的区别

SQL> conn yang/yang as sysdba
已连接。
SQL> create table t as select object_id id ,object_name name
  2  from dba_objects ;
表已创建。
SQL> set autot traceonly
一次普通的全表扫描，没有排序的！
SQL> select id ,name from t;
已选择68372行。
执行计划
----------------------------------------------------------                      
Plan hash value: 1601196873                                                    
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      | 64794 |  4998K|    91   (2)| 00:00:02 |      
|   1 |  TABLE ACCESS FULL| T    | 64794 |  4998K|    91   (2)| 00:00:02 |      
--------------------------------------------------------------------------      
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------                      
         68  recursive calls                                                    
          0  db block gets                                                      
       4943  consistent gets                                                    
        325  physical reads                                                     
          0  redo size                                                          
    2936793  bytes sent via SQL*Net to client                                   
      50554  bytes received via SQL*Net from client                             
       4560  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
      68372  rows processed    
--根据id 排序！注意执行计划里面的TempSpc 是临时空间 seo顾问,大小11M
SQL> select id ,name from t order by id;
已选择68372行。
执行计划
----------------------------------------------------------                      
Plan hash value: 961378228                                                      
----------------------------------------------------------------------------------               
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 64794 |  4998K|    |  1283   (1)| 00:00:16 |
|   1 |  SORT ORDER BY     |      | 64794 |  4998K|    11M|  1283   (1)| 00:00:16 |
|   2 |   TABLE ACCESS FULL| T    | 64794 |  4998K|       |    91   (2)| 00:00:02 |
-----------------------------------------------------------------------------------
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement                                   
统计信息
----------------------------------------------------------                      
          4  recursive calls                                                    
          0  db block gets                                                      
        394  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
    2663362  bytes sent via SQL*Net to client                                   
      50554  bytes received via SQL*Net from client                             
       4560  SQL*Net roundtrips to/from client                                  
          1  sorts (memory)                                                     
          0  sorts (disk)                                                       
      68372  rows processed                                                     

--在表的 id 字段建立索引，并进行信息统计。
SQL> create index idx_id on t(id) ;
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user ,'T',cascade => true);
PL/SQL 过程已成功完成。

SQL> select id ,name from t order by id;
已选择68372行。
执行计划
----------------------------------------------------------                      
Plan hash value: 961378228                                                     
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time   |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 68372 |  1936K|       |   638   (1)| 00:00:08 |
|   1 |  SORT ORDER BY     |      | 68372 |  1936K|  5384K|   638   (1)| 00:00:08 |
|   2 |   TABLE ACCESS FULL| T    | 68372 |  1936K|       |    91   (2)| 00:00:02 |
-----------------------------------------------------------------------------------              
统计信息
----------------------------------------------------------                      
        151  recursive calls                                                    
          0  db block gets                                                      
        348  consistent gets                                                    
          0  physical reads                                                     
          0  redo size                                                          
    2663362  bytes sent via SQL*Net to client                                   
      50554  bytes received via SQL*Net from client                             
       4560  SQL*Net roundtrips to/from client                                  
          5  sorts (memory) --没有使用索引比全表扫描多了四此排序。
          0  sorts (disk)                                                       
      68372  rows processed    
--使用索引。执行计划中没有tempspac                                              
SQL> select id ,name from t where id <1200 order by id;--加上了order by
已选择1133行。
执行计划
----------------------------------------------------------
Plan hash value: 827754323
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  1167 | 33843 |    10   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |  1167 | 33843 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ID |  1167 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"<1200)
统计信息
----------------------------------------------------------
          1  recursive calls  
          0  db block gets   
        160  consistent gets
          0  physical reads  
          0  redo size   
      37872  bytes sent via SQL*Net to client  
       1241  bytes received via SQL*Net from client
         77  SQL*Net roundtrips to/from client   
          0  sorts (memory) --这里可以看出没有排序！
          0  sorts (disk)   
       1133  rows processed


小结：
如果数据直接从索引获取，也是有序的，此时加order by,cbo不会执行sort 排序动作的。即，加上order by对性能也不会有什么影响！
其实这里还是有疑问的
1 根据id 排序 走全表扫描和建立了索引后信息统计上有差别，前者比后者少了3个sort 操作！而后者的TempSpc比全表扫描少了将近一半！
2 关于TempSpc 的理解如果是临时表空间 ，就用到了磁盘排序了 ，而执行上面没有显示disk sort！