如果经常使用类似于以下的查询来获取数据,有序散列聚簇就最适合:
Select *
From t
Where KEY=:x
Order by SORTED_COLUMN
也就是说,要按某个键获取数据,但要求这些数据按另外每个列排序。通过使用有序散列聚簇,Oracle可以返回数据而根据不用执行排序。这是通过插入时按键的有序物理存储数据做到的。
假设有一个客户订单表:
select cust_id, order_dt, order_number
from cust_orders
order by cust_id, order_dt;
CUST_ID ORDER_DT ORDER_NUMBER
------- ----------------------------- -------------
1 31-MAR-05 09.13.57.000000 PM 21453
11-APR-05 08.30.45.000000 AM 21454
28-APR-05 06.21.09.000000 AM 21455
2 08-APR-05 03.42.45.000000 AM 21456
19-APR-05 08.59.33.000000 AM 21457
27-APR-05 06.35.34.000000 AM 21458
30-APR-05 01.47.34.000000 AM 21459
这个表存储在一个有序散列聚簇中,在此HASH键是CUST_ID,并按ORDER_DT字段排序。
创建 有序散列聚簇 语句:
system@ORCL>CREATE CLUSTER shc
2 (
3 cust_id NUMBER,
4 order_dt timestamp SORT
5 )
6 HASHKEYS 10000
7 HASH IS cust_id
8 SIZE 8192
9 /
簇已创建。
这 里引入了一个新的关键字:SORT。创建聚簇时,我们标识了HASH IS CUST_ID,而且用关键字SORT增加了一个时间戳(timestamp)类型的ORDER_DT。这说明,数据将按CUST_ID查找(查找条件是 CUST_ID = :X),而按ORDER_DT物理地获取和排序。从技术上讲,实际上这表示我们存储的数据将通过一个NUMBER列获取,但按一个TIMESTAMP列排序。
这个CUST_ORDERS的相应CREATE TABLE语句如下所示:
system@ORCL>CREATE TABLE cust_orders
2 ( cust_id number,
3 order_dt timestamp SORT,
4 order_number number,
5 username varchar2(30),
6 ship_addr number,
7 bill_addr number,
8 invoice_num number
9 )
10 CLUSTER shc ( cust_id, order_dt )
11 /
表已创建。
我们就这个表的CUST_ID列映射到有序散列聚簇的散列键,并把ORDER_DT列映射到SORT列。使用SQL*Plus中的AUTOTRACE,可以观察到,访问有序散列聚簇时,原本以为有的正常排序操作不见了:
system@ORCL>set autotrace traceonly explain
system@ORCL>variable x number
system@ORCL>select cust_id, order_dt, order_number
2 from cust_orders
3 where cust_id = :x
4 order by order_dt;
执行计划
----------------------------------------------------------
Plan hash value: 465084913
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)|
|* 1 | TABLE ACCESS HASH| CUST_ORDERS | 1 | 39 | |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CUST_ID"=TO_NUMBER(:X))
Note
-----
- dynamic sampling used for this statement (level=2)
system@ORCL>select job, hiredate, empno
2 from scott.emp
3 where job = 'CLERK'
4 order by hiredate;
执行计划
----------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 60 | 4 (25)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 60 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JOB"='CLERK')
我对平常的SCOTT.EMP表做了一个查询(为了便于说明,这里现在JOB列上加了索引),这样可以看到我们预想的情况,以便做个比较:在上面的例子中, 后面展示的是SCOTT.EMP查询计划,前面则展示了希望以FIFO模式(像队列一样)访问数据时有序散列聚簇会为我们做什么。可以看到,有序散列聚簇 只完成了一步:它取得CUST_ID = :X,对输入执行散列,找到第一行,如何开始读取这一行,因为有序散列聚簇中行已经是有序的。常规表则有很大不同:它会查找所有JOB=‘CLERK’行 (可能分布在堆表中的各个地方),对它们进行排序,再返回第一行。
所以,有序散列聚簇具备散列聚簇在获取方面的所有特点,因为它能得到数据而不必遍历索引;另外有序散列聚簇还拥有IOT的许多特性,因为数据可以按你选择的 每个字段(键)有序地存储。当输入数据按排序字段(键)的顺序到达时,这种数据结构就能很好地工作。也就是说,在一段时间内,数据按某个给定键值的递增有序顺序到达。股票信息就满足这个要求。每天晚上你会得到一个新文件,其中填满了股票代码、日期以及相关的信息(日期是排序键,股票代码是散列键)。你按排序键顺序地接收好加载这些数据。对于股票代码ORCL,昨天的股票数据不会在今天之后才到达,你会先加载昨天的值,然后才是今天的值,之后是明天的值。如果信息随机地到达(不按有序的顺序到来),插入过程中,这个数据结构很快就会受不了,因为必须移动大量的数据使得这些行在磁盘上物理有序。在这种情况下, 不建议采用有序散列聚簇(此时采用IOT可能很合适)。
使用这个结构时,应当考虑到散列聚簇同样的问题,另外还要考虑到一个约束条件,即数据应该按键值的有序顺序到达。