Oracle's cluster and cluster tables

Oracle's cluster and cluster tables

A cluster consists of a group of tables that share the same data block, which stores the related rows of those tables in the same data block, which reduces the amount of disk reads required to query the data. After the cluster is created, users can create tables in the cluster that become cluster tables.

For example, the Scott user has an employee information sheet EMP, a Departmental Information table dept, and two tables that share the department number deptno column. When the EMP and Dept tables are clustered, the physical Oracle stores all employee information and department information lines for each department in the EMP and dept tables into the same data block. Because clusters store related rows of different tables in the same data block, proper use of clusters can help reduce the amount of disk reads required to query the data. For tables that are often used alone, you should not use clusters.

Create a cluster

The syntax for creating clusters is as follows:

CREATE CLUSTER cluster_name(COLUMN date_type[,COLUMN date_type]...40101255 | INTEGER][TABLESPACE tablespace_name][STORAGE storage]

Example 1:
Log in with the SYSDBA role and give the user the appropriate permissions:

GRANT  ALTER ANY CLUSTER TO siege;

Then create the cluster:

CREATE CLUSTER cluster_student(sid NUMBER)PCTUSED 40PCTFREE 10SIZE 1024STORAGE (        INITIAL 128k        NEXT 128k        MINEXTENTS 2        MAXEXTENTS 20)TABLESPACE learning;

Oracle's cluster and cluster tables