CLUSTER

CLUSTER
Definition:
In Oracle, clustering is a way of ordering the physical placement of tables on a disk drive in such a way as
to speed up I/O access times. This is accomplished by sharing data blocks on the disk. Anything that
minimizes I/O or reduces contention in the physical file system will improve the overall performance of
the database.
A table cluster can be described as a set of tables that share the same data blocks, and that are grouped
together because they share common columns that are often used together. Index cluster tables can be
either multi-table or single-table.
When cluster tables are created, Oracle stores all of the rows for each of the tables in the same data
blocks. The cluster key value is the value of the cluster key columns for a particular row.
An index cluster uses an index to maintain row sequence, as opposed to a hash cluster, where the
symbolic key is hashed to the data block address.
A cluster provides an optional method of storing table data. A cluster is made up of a group of tables
that share the same data blocks. The tables are grouped together because they share common columns
and are often used together. For example, the emp and dept table share the deptno column. When you
cluster the emp and dept tables, Oracle Database physically stores all rows for each
department from both the emp and dept tables in the same data blocks.
Because clusters store related rows of different tables together in the same data blocks, properly used
clusters offer two primary benefits:
• Disk I/O is reduced and access time improves for joins of clustered tables.
• The cluster key is the column, or group of columns, that the clustered
tables have in common. You specify the columns of the cluster key when
creating the cluster. You subsequently specify the same columns when
creating every table added to the cluster. Each cluster key value is stored
only once each in the cluster and the cluster index, no matter how many rows
of different tables contain the value.
Therefore, less storage might be required to store related table and index data in a cluster than is
necessary in non-clustered table format. For example, in Figure 1, notice how each cluster key (each
deptno) is stored just once for many rows that contain the same value in both the emp and dept tables.
After creating a cluster, you can create tables in the cluster. However, before any rows can be inserted
into the clustered tables, a cluster index must be created. Using clusters does not affect the creation of
additional indexes on the clustered tables; they can be created and dropped as usual.
You should not use clusters for tables that are frequently accessed individually.

Data Dictionary Objects
user_clusters
user_clu_columns
user_cluster_hash _expressions
user_all_tables


Clustering is a method of storing tables that are intimately related and often joined together into the
same area on disk. The cluster key is the column or columns by which the tables are usually joined in a
query
By storing the field comprising the Cluster Key once instead of multiple times, storage is saved. The
arguably more significant advantage to Clustering is to expidite join queries. When a query is fired that
joins these 2 tables by Cluster Key, the joined rows would be fetched with a single IO operation.
A cluster is a data structure that improves retrieval performance
Example:
----------------------------------------------------------

create cluster empdept (did number(2));
----------------------------------------------------------
create index empdept_indx on cluster empdept;
----------------------------------------------------------
create table emp
(
eid number(10),
ename varchar2(100),
did number(2)
)
cluster empdept(did);
----------------------------------------------------------
create table dept
(
did number(2),
dname varchar2(100)
)
cluster empdept(did);
----------------------------------------------------------