Oracle Using Sorted Hash Clusters

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Sorted hash clusters are new data structures that allow faster retrieval of data for applications where data is consumed in the order in which it was inserted.

In a sorted hash cluster, the table�s rows are already presorted by the sort key column.

Here are some of its main features:

� You can create indexes on sorted hash clusters.

� You must use the cost-based optimizer, with up-todate statistics on the sorted hash cluster tables.

� You can insert row data into a sorted hash clustered table in any order, but Oracle recommends inserting them in the sort key column order, since it�s much faster.

Creating Sorted Hash Cluster

CREATE CLUSTER call_cluster
(call_number NUMBER,
call_timestamp NUMBER SORT,
call_duration NUMBER SORT)
HASHKEYS 10000
SINGLE TABLE
HASH IS call_number
SIZE 50;

SINGLE TABLE	indicates that the cluster is a type of hash cluster containing only one table.
HASH IS expr	Specifies an expression to be used as the hash function for the hash cluster.
HASHKEYS	this clause creates a hash cluster and specify the number of hash values for the hash cluster.
SIZE	Specify the amount of space in bytes reserved to store all rows with the same cluster key value or the same hash value.

CREATE TABLE calls
(call_number NUMBER,
call_timestamp NUMBER,
call_duration NUMBER,
call_info VARCHAR2(50))
CLUSTER call_cluster
(call_number,call_timestamp,call_duration)

Partitioned IOT Enhancements

The following are the newly supported options for partitioned index-organized tables (IOTs):

� List-partitioned IOTs: All operations allowed on list partitioned are now   supported for IOTs.

� Global index maintenance: With previous releases of the Oracle   database, the global indexes on partitioned IOTs were not maintained when   partition maintenance operations were performed. After DROP,     TRUNCATE, or EXCHANGE PARTITION, the global indexes became   UNUSABLE. Other partition maintenance operations such as MOVE,   SPLIT, or MERGE PARTITION did not make the global indexes   UNUSABLE, but the performance of global index�based access was   degraded because the guess�database block addresses stored in the index   rows were invalidated. Global index maintenance prevents these issues   from happening, keeps the index usable, and also maintains the guess�data   block addresses.

� Local partitioned bitmap indexes: The concept of a mapping table is   extended to support a mapping table that is equi-partitioned with respect to   the base table. This enables the creation of bitmap indexes on partitioned   IOTs.

� LOB columns are now supported in all types of partitioned IOTs.