Database Tables - Expert Oracle Database Architecture - page 401

Hash clusters are suitable in the following situations:

• You know with a good degree of accuracy how many rows the table will have over its life, or

you have some reasonable upper bound. Getting the size of the HASHKEY s and SIZE parameters
right is crucial to avoid a rebuild.

• DML, especially inserts, is light with respect to retrieval. This means you have to balance

optimizing data retrieval with new data creation. Light inserts might be 100,000 per unit of
time for one person and 100 per unit of time for another—all depending on their data retrieval
patterns. Updates do not introduce significant overhead, unless you update the HASHKEY ,
which would not be a good idea as it would cause the row to migrate.

• You access the data by the

HASHKEY value constantly. For example, say you have a table of
parts, and these parts are accessed by part number. Lookup tables are especially appropriate
for hash clusters.

Sorted Hash Clustered Tables
Sorted hash clusters are available in Oracle 10 g and above. They combine the qualities of the hash cluster just described
with those of an IOT. They are most appropriate when you constantly retrieve data using a query similar to this:

Select *
From t
Where KEY =:x
Order by SORTED_COLUMN

That is, you retrieve the data by some key and need that data ordered by some other column. Using a sorted hash
cluster, Oracle can return the data without performing a sort at all. It accomplishes this by storing the data upon insert
in sorted order physically—by key. Suppose you have a customer order table:

EODA@ORA12CR1> select cust_id, order_dt, order_number
2 from cust_orders
3 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
7 rows selected.

The table is stored in a sorted hash cluster, whereby the HASH key is CUST_ID and the field to sort on is ORDER_DT .
Graphically, it might look like Figure 10-10 , where 1, 2, 3, 4, . . . represent the records stored sorted on each block.