We might create the cluster for orders as in the following example:
create cluster order_cluster (order_number number(6))
size 50
hash is order_number hashkeys 1000000;
create table cust_order (
order_number number(6) primary key,
order_date date,
customer_number number)
cluster order_cluster(order_number);
Hash clusters have performance benefits when you select rows from a table using an equality
comparison, as in this example:
select order_number, order_date from cust_order
where order_number = 196811;
Typically, this kind of query will retrieve the row with only one I/O if the number of hashkeys
is high enough and the hash is clause, containing the hashing function, produces an evenly
distributed hash key.
Sorted Hash Clusters
Sorted hash clusters are new as of Oracle 10g. They are similar to regular hash clusters in that a
hashing function is used to locate a row in a table. However, in addition, sorted hash clusters
allow rows in the table to be stored by one or more columns of the table in ascending order. This
14 Oracle Database 11g DBA Handbook
allows the data to be processed more quickly for applications that lend themselves to first in, first
out (FIFO) processing.
You create sorted hash clusters using the same syntax as regular clustered tables, with the
addition of the SORT positional parameter after the column definitions within the cluster. Here is
an example of creating a table in a sorted hash cluster:
create table order_detail (
order_number number,
order_timestamp timestamp sort,
customer_number number)
cluster order_detail_cluster (
order_number,
order_timestamp);
Due to the FIFO nature of a sorted hash cluster, when orders are accessed by order_number
the oldest orders are retrieved first based on the value of order_timestamp.
Pages:
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73