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 是临时空间,大小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！
对这两个问题问个为什么？
