Oracle clusters and cluster tables

Oracle clusters and cluster tables

A cluster consists of multiple tables that share the same data block. It stores the related rows of these tables in the same data block, which can reduce the disk read volume required to query data. After creating a cluster, you can create tables in the cluster. These tables become cluster tables.

For example, the scott user has an employee information table (emp) and a department information table (dept). The two tables share the Department number (deptno) column. After the emp and dept tables are clustered, Oracle physically stores all employee information and Department Information rows in the emp and dept tables for each department in the same data block. Because clusters store related rows of different tables in the same data block, the rational use of clusters can help reduce the disk read volume required for data query. Instead of using clusters for tables that are often used separately.

Create a cluster
The syntax for creating a cluster is as follows:

Create cluster cluster_name (COLUMN date_type [, COLUMN date_type]...)
[PCTUSED 40 | INTEGER]
[PCTFREE 10 | INTEGER]
[Size integer]
[INITRANS 1 | INTEGER]
[MAXTRANS 255 | INTEGER]
[TABLESPACE tablespace_name]
[STORAGE storage] 1
Example 1:
Log On As SYSDBA and grant related permissions to the user:

Grant alter any cluster to siege; 1

Then create a cluster:

Create cluster cluster_student (sid NUMBER)
PCTUSED 40
PCTFREE 10
SIZE 1024
STORAGE (
Initialize 128 k
NEXT 128 k
MINEXTENTS 2
MAXEXTENTS 20
) TABLESPACE learning;