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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

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

對這兩個問題問個為什麼？

【SQL 學習】排序問題之order by與索引排序

相關文章