Databases Reference
In-Depth Information
Clusters may not be appropriate for tables that regularly require full table scans, in which
a query requires the Oracle Database to iterate through all the rows of the table. Because
you access a cluster table through the cluster index, which then points to a data block,
full table scans on clustered tables can actually require more I/O operations, lowering
overall performance.
Hash Clusters
A
hash cluster
is like a cluster with one significant difference that makes it even faster.
Each request for data in a clustered table involves at least two I/O operations, one for
the cluster index and one for the data. A hash cluster stores related data rows together,
but groups the rows according to a
hash value
for the cluster key. The hash value is
calculated with a hash function, which means that each retrieval operation starts with
a calculation of the hash value and then goes directly to the data block that contains the
relevant rows.
By eliminating the need to go to a cluster index, a hash-clustered table can be even faster
for retrieving data than a clustered table. You can control the number of possible hash
values for a hash cluster with the HASHKEYS parameter when you create the cluster.
Because the hash cluster directly points to the location of a row in the table, you must
allocate all the space required for all the possible values in a hash cluster when you create
the cluster.
Hash clusters work best when there is an even distribution of rows among the various
values for the hash key. You may have a situation in which there is already a unique
value for the hash key column, such as a unique ID. In such situations, you can assign
the value for the hash key as the value for the hash function on the unique value, which
eliminates the need to execute the hash function as part of the retrieval process. In
addition, you can specify your own hash function as part of the definition of a hash
cluster.
Oracle Database 10
g
introduced sorted hash clusters, where data is not only stored in a
cluster based on a hash value, but is also stored within that location in the order in which
it was inserted. This data structure improves performance for applications that access
data in the order in which it was added to the database.
Extended Logic for Data
There are several features that have been added to the Oracle Database that are not
unique data structures, but rather shape the way you can use the data in the database:
the Rules Manager and the Expression Filter.
