10g新增排序雜湊聚集表
Oracle的10g對HASH CLUSTER表增加了新功能，允許HASH CLUSTER表對於指定HASH鍵值的記錄按照指定列的順序存放。而這個功能有點像是HASH CLUSTER表和索引組織表的結合體。
首先看看如何建立一個HASH SORT CLUSTER：
SQL> CREATE CLUSTER C_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT)
3 HASHKEYS 5000 SIZE 100;
Cluster created.
SQL> CREATE TABLE T_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT, NAME VARCHAR2(30))
3 CLUSTER C_HASH_SORT (ID, CREATED);
Table created.
SQL> BEGIN
2 FOR I IN 1..1000 LOOP
3 FOR J IN 1..50 LOOP
4 INSERT INTO T_HASH_SORT
5 VALUES (I, SYSDATE - 51 + J, 'OBJECT' || I ||J);
6 END LOOP;
7 END LOOP;
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM T_HASH_SORT;
COUNT(*)
----------
50000
接下來看看什麼樣的查詢可以從這種結構的表中獲得效能優勢：
SQL> SET AUTOT ON
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> SELECT *
2 FROM T_HASH_SORT
3 WHERE ID = 352
4 ORDER BY CREATED;
ID CREATED NAME
---------- ------------------- ------------------------------
352 2009-05-22 16:30:07 OBJECT3521
352 2009-05-23 16:30:07 OBJECT3522
352 2009-05-24 16:30:07 OBJECT3523
352 2009-05-25 16:30:07 OBJECT3524
352 2009-05-26 16:30:07 OBJECT3525
352 2009-05-27 16:30:07 OBJECT3526
352 2009-05-28 16:30:07 OBJECT3527
.
.
.
352 2009-07-09 16:30:07 OBJECT35249
352 2009-07-10 16:30:07 OBJECT35250
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1422057106
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 897 | 0 (0)|
|* 1 | TABLE ACCESS HASH| T_HASH_SORT | 23 | 897 | |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=352)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
2469 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
可以看到，上面的查詢只執行了一個TABLE ACCESS HASH，根本沒有執行排序的操作，這是因為資料本身就是根據CREATED欄位的順序儲存的。
SQL> CREATE TABLE T_NORMAL
2 (ID NUMBER, CREATED DATE, NAME VARCHAR2(30));
Table created.
SQL> BEGIN
2 FOR I IN 1..1000 LOOP
3 FOR J IN 1..50 LOOP
4 INSERT INTO T_NORMAL
5 VALUES (I, SYSDATE - 51 + J, 'OBJECT' || I ||J);
6 END LOOP;
7 END LOOP;
8 COMMIT;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> SELECT *
2 FROM T_NORMAL
3 WHERE ID = 352
4 ORDER BY CREATED;
ID CREATED NAME
---------- ------------------- ------------------------------
352 2009-05-22 22:19:30 OBJECT3521
352 2009-05-23 22:19:30 OBJECT3522
352 2009-05-24 22:19:30 OBJECT3523
352 2009-05-25 22:19:30 OBJECT3524
352 2009-05-26 22:19:30 OBJECT3525
.
.
.
352 2009-07-09 22:19:30 OBJECT35249
352 2009-07-10 22:19:30 OBJECT35250
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1781898017
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 3744 | 44 (5)| 00:00:01 |
| 1 | SORT ORDER BY | | 96 | 3744 | 44 (5)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T_NORMAL | 96 | 3744 | 43 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=352)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
125 consistent gets
0 physical reads
0 redo size
2469 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50 rows processed
對於普通表來說，不但需要掃描表，還需要排序操作，代價就要大得多。
當然普通表可以新增索引改善情況：
SQL> CREATE INDEX IND_T_NORMAL ON T_NORMAL (ID, CREATED);
Index created.
SQL> SELECT *
2 FROM T_NORMAL
3 WHERE ID = 352
4 ORDER BY CREATED;
ID CREATED NAME
---------- ------------------- ------------------------------
352 2009-05-22 22:19:30 OBJECT3521
352 2009-05-23 22:19:30 OBJECT3522
352 2009-05-24 22:19:30 OBJECT3523
352 2009-05-25 22:19:30 OBJECT3524
352 2009-05-26 22:19:30 OBJECT3525
.
.
.
352 2009-07-09 22:19:30 OBJECT35249
352 2009-07-10 22:19:30 OBJECT35250
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3493887494
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 1950 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_NORMAL | 50 | 1950 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_NORMAL | 50 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=352)
filter("ID"=352)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
2469 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
即使新增了索引，所需要的物理讀也要比HASH SORT CLUSTER要多，因為Oracle先要訪問索引，在索引找到對應的鍵值，然後根據索引的ROWID去讀取表。這個操作代價要比透過HASH演算法直接找到表的行記錄的代價高得多。
當然普通表建立索引有更好的靈活性，使用更多更靈活的情況，而HASH SORT CLUSTER只對特定的查詢有效，查詢必須提供HASH鍵值列的值，而且必須對指定的列進行排序。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-608907/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 雜湊表（雜湊表）詳解
- 雜湊表（雜湊表）原理詳解
- 雜湊表
- <hash命令：顯示、新增或清除雜湊表>
- 【尋跡#3】 雜湊與雜湊表
- 字串雜湊表字串
- 6.7雜湊表
- 查詢(3)--雜湊表(雜湊查詢)
- 347前 K 個高頻元素（雜湊表、堆排序）排序
- 深入理解雜湊表（JAVA和Redis雜湊表實現）JavaRedis
- 雜湊表應用
- 雜湊表的原理
- 實現雜湊表
- 淺談雜湊表
- oracle 10g 新增:自定義聚集函式Oracle 10g函式
- 符號表與雜湊表符號
- 排序（並查集&&雜湊函式）排序並查集函式
- 【資料結構】查詢結構（二叉排序樹、ALV樹、雜湊技術雜湊表）資料結構排序
- 幾道和雜湊（雜湊）表有關的面試題面試題
- R語言——雜湊表R語言
- JAVA 實現 - 雜湊表Java
- 【閱讀筆記：雜湊表】Javascript任何物件都是一個雜湊表（hash表）！筆記JavaScript物件
- 雜湊技術【雜湊表】查詢演算法 PHP 版演算法PHP
- 【資料結構與演算法學習】雜湊表（Hash Table，雜湊表）資料結構演算法
- 資料結構——雜湊表資料結構
- freeswitch APR庫雜湊表
- 談談面試--雜湊表系列面試
- 雜湊表(Hash)的應用
- 雜湊表的一點思考
- 資料結構之「雜湊表」資料結構
- 雜湊表的兩種實現
- 從Dictionary原始碼看雜湊表原始碼
- 雜湊表知識點小結
- iOS雜湊表快取窺探iOS快取
- 演算法學習-雜湊表演算法
- PHP雜湊表碰撞攻擊原理PHP
- 資料結構 - 雜湊表，初探資料結構
- 雜湊表hashtable課堂筆記筆記