Database Reference
In-Depth Information
read from overflow will be extremely heavy on performance. Clusters are
intended for data warehouses.
A standard cluster stores index columns for multiple tables and some or
all nonindexed columns. A cluster simply organizes parts of tables into a
combination index and data space sorted structure. Datatypes must be con-
sistent across tables.
21.2.2
Types of Clusters
Regular Cluster
. This is simply a cluster.
Hash Cluster
. A cluster indexed using a hashing algorithm. Hash
clusters are more efficient than standard clusters and are even more
appropriate for read-only type data. In older relational databases,
hash indexes were often used against integer values for better data
access speed. If data was changed, the hash index had to be rebuilt.
Sorted Hash Cluster
. Uses the SORT option shown in Figure
21.7, essentially breaking up data into groups of hash values. Hash
values are derived from a cluster key value, forcing common rows to
be stored in the same physical location. A sorted hash cluster has an
additional performance benefit for queries accessing rows in the order
in which the hash cluster is ordered, thus the term
sorted hash cluster
.
21.2.3
Creating Clusters
I always find it a little confusing attempting to classify a cluster as a table or an
index. Because clusters have aspects of both, I find it wise to include an expla-
nation of clusters with that of indexing, after tables have been explained.
Tables are covered in Chapter 18. In simple terms, a cluster is a database
object that when created has tables added to it. A cluster is not a table, even
though it is created using a CREATE TABLE command. Figure 21.7 shows a
syntax diagram containing syntax details relevant to creating a cluster.
Note:
There is an ALTER CLUSTER command, but it only allows physical
changes; thus, it is database administration and irrelevant to the Oracle
SQL content of this topic.
Let's look at a simple example. Note that in the following example, we
have created both a cluster and a cluster index.
