Databases Reference
In-Depth Information
If you are thinking that cluster tables are something exotic, probably
not used in real world, take a look at the
CLUSTER_NAME
column of
DBA_TABLES
:
select table_name, cluster_name from dba_tables
where cluster_name is not null;
You will discover that some data dictionary tables are stored in clusters.
Consider introducing clusters in situations where data is almost static and you often query
joined together tables.
Cluster size
When we have created the cluster, we have specified
SIZE
100
in the
CREATE
CLUSTER
statement.
This optional parameter specifies the estimated size—in bytes—required to store an average
cluster key with related rows, which share the same cluster key value.
This value is used by the database to estimate the number of different cluster key values,
which will be stored in a database block, to optimize the storage space for the cluster.
In our example, we have deliberately used a very small sized factor to force Oracle to store a
lot of different key values in the same data block, for the sake of illustration.
Cluster index
We have built an index on the cluster key; this is a common index, except for two aspects:
F
The cluster index stores only one entry for every key value, pointing to the database
block that contains the rows with that particular key value.
F
The cluster index stores an entry for the null key value too. This is a peculiar behavior,
because the standard indexes don't store entries when the indexed fields contain all
null values.
Clustering and truncating
Another circumstance when table clustering is not appropriate is when we have tables that need
to be truncated. Due to the particular storage characteristics of a clustered table, we cannot
perform a
TRUNCATE TABLE
statement, because the database blocks are shared among the
tables in the cluster. The only way to eliminate the rows is with the
DELETE
command.
If we need to use the
TRUNCATE TABLE
statement on a table, clustering is not a choice.
