Database Reference
In-Depth Information
CUST_ID=1 data
CUST_ID=2 data
1
1
2
2
3
3
4
Figure 10-10.
Depiction of a sorted hash cluster
Creating a sorted hash cluster is much the same as the other clusters. To set up a sorted hash cluster capable of
storing the preceding data, we could use the following:
EODA@ORA12CR1> 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 /
Cluster created.
We've introduced a new keyword here:
SORT
. When we created the cluster, we identified the
HASH IS CUST_ID
and we added an
ORDER_DT
of type timestamp with the keyword
SORT
. This means the data will be located by
CUST_ID
(where
CUST_ID=:x
) and physically retrieved sorted by
ORDER_DT
. Technically, it really means we'll store some data
that will be retrieved via a
NUMBER
column and sorted by the
TIMESTAMP
. The column names here are not relevant, as
they were not in the B*Tree or
HASH
clusters, but convention would have us name them after what they represent.
The
CREATE TABLE
statement for our
CUST_ORDERS
table would look like this:
EODA@ORA12CR1> 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 /
Table created.
