Databases Reference
In-Depth Information
The syntax to define sorted hash clusters is as follows:
CREATE CLUSTER ORDER_CLUSTER (orderid NUMBER(4))
SIZE 8192 HASHKEYS 100;
CREATE TABLE ORDERS(
orderid NUMBER(4),
date_placed DATE,
customer_id NUMBER(4)
) CLUSTER ORDER_CLUSTER(orderid);
CREATE TABLE ORDER_DETAILS (
orderid NUMBER(4),
part_id NUMBER(4),
price NUMBER(9,2) sort,
description VARCHAR2(30)
) CLUSTER ORDER_CLUSTER(orderid);
In this example, we have created the hash cluster
ORDER_CLUSTER
to store order data
with their details; these are sorted by the
price
column.
Custom hash function
By default, Oracle provides us with a hash function to generate the values to be used in
hash clusters.
If we prefer, we can define our own hash function for a cluster, using the HASH IS clause in
the
CREATE
CLUSTER
statement. This capability is enabled only when the cluster key is
made up by a single integer column.
Our hash function must evaluate to a positive value, needs to reference at least one field,
and cannot reference PL/SQL functions. There are other restrictions, listed in the official
Oracle documentation (
http://docs.oracle.com/cd/E11882_01/server.112/
e26088/toc.htm
).
Single-table hash clusters
We can create a hash cluster consisting of a single table only, defining a so-called Single-table
hash cluster.
This might seem an oxymoron: a cluster—which is to store more than one table in the same
database block—made by a single table.
