【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

<b>一次普通的全表扫描，没有排序的！</b>

sql&gt; 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 |  <b>table access full</b>| 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                                  

          <b>0  sorts (memory)                                                     </b>

<b>          0  sorts (disk)    </b>                                                   

      68372  rows processed    

<b>--根据id 排序！注意执行计划里面的tempspc 是临时空间,大小11m</b>

sql&gt; select id ,name from t order by id;

plan hash value: 961378228                                                      

----------------------------------------------------------------------------------               

| id  | operation          | name | rows  | bytes |<b>tempspc</b>| cost (%cpu)| time | 

--------------------------------------------------------------------------------

|   0 | select statement   |      | 64794 |  4998k|    |  1283   (1)| 00:00:16 | 

|   1 |  <b>sort order by</b>     |      | 64794 |  4998k|    <b>11m</b>|  1283   (1)| 00:00:16 | 

|   2 |   table access full| t    | 64794 |  4998k|       |    91   (2)| 00:00:02 |

----------------------------------------------------------------------------------- 

   - dynamic sampling used for this statement                                   

          4  recursive calls                                                    

        394  consistent gets                                                    

          0  physical reads                                                     

    2663362  bytes sent via sql*net to client                                   

          1  sorts (memory)                                                     

          0  sorts (disk)                                                       

      68372  rows processed                                                     

--在表的 id 字段建立索引，并进行信息统计。

sql&gt; create index idx_id on t(id) ;

索引已创建。

sql&gt; exec dbms_stats.gather_table_stats(user ,'t',cascade =&gt; true);

pl/sql 过程已成功完成。

plan hash value: 961378228                                                     

-----------------------------------------------------------------------------------

| id  | operation          | name | rows  | bytes |<b>tempspc</b>| cost (%cpu)| time   | 

|   0 | select statement   |      | 68372 |  1936k|       |   638   (1)| 00:00:08 |

|   1 |  <b>sort order by</b>     |      | 68372 |  1936k|  5384k|   638   (1)| 00:00:08 |

|   2 |   table access full| t    | 68372 |  1936k|       |    91   (2)| 00:00:02 |

-----------------------------------------------------------------------------------              

        151  recursive calls                                                    

        348  consistent gets                                                    

          <b>5  sorts (memory) --没有使用索引比全表扫描多了四此排序</b>。

--使用索引。执行计划中没有tempspac                                              

sql&gt; select id ,name from t where id &lt;1200 <b>order by id</b>;<b>--加上了order by</b>

已选择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"&lt;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   

         <b> 0  sorts (memory) --这里可以看出没有排序！</b>

          0  sorts (disk)   

       1133  rows processed 

小结：

<b>如果数据直接从索引获取，也是有序的，此时加order by,cbo不会执行sort 排序动作的。即，加上order by对性能也不会有什么影响！</b>

其实这里还是有疑问的

1 根据id 排序 走全表扫描和建立了索引后信息统计上有差别，前者比后者少了3个sort 操作！而后者的tempspc比全表扫描少了将近一半！

2 关于tempspc 的理解如果是临时表空间 ，就用到了磁盘排序了 ，而执行上面没有显示disk sort！

 <b> 对这两个问题问个为什么？</b>