cluster

In Oracle Database, a cluster is a schema object that stores rows from one or more tables that have a common column in the same data blocks. This improves the performance of joins and queries on the common column because related rows are stored physically close together.

Types of Clusters in Oracle

1. Indexed Cluster
   • Uses a cluster index to locate rows.
   • Best when queries frequently join tables using the cluster key.
2. Hash Cluster
   • Uses a hash function instead of an index.
   • Provides very fast retrieval for equality searches on the cluster key.

Example

Suppose you have two tables:

• DEPARTMENTS (DEPTNO, DNAME)
• EMPLOYEES (EMPNO, ENAME, DEPTNO)

Both tables share the column DEPTNO.

Without a cluster:

• Data from both tables is stored separately.

With a cluster:

• Rows having the same DEPTNO are stored together in the same data blocks, making joins faster.

Creating an Indexed Cluster

CREATE CLUSTER emp_dept_cluster
(DEPTNO NUMBER(2))
SIZE 512;

CREATE INDEX emp_dept_cluster_idx
ON CLUSTER emp_dept_cluster;

CREATE TABLE departments
(
    deptno NUMBER(2),
    dname VARCHAR2(20)
)
CLUSTER emp_dept_cluster(deptno);

CREATE TABLE employees
(
    empno NUMBER(4),
    ename VARCHAR2(20),
    deptno NUMBER(2)
)
CLUSTER emp_dept_cluster(deptno);

Advantages

• Faster joins on the cluster key.
• Fewer disk I/O operations.
• Efficient retrieval of related data.

Disadvantages

• More complex to manage.
• Can waste space if cluster sizing is poor.
• Not suitable when tables are rarely joined or the cluster key changes frequently.