Oracle cluster and cluster tables, Oracle cluster tables

Oracle cluster and cluster tables, Oracle 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]

Example 1:
Log On As SYSDBA and grant related permissions to the user:

GRANT CREATE  ANY CLUSTER TO siege;

Then create a cluster:

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

Create a cluster Table

After creating a cluster, create a cluster Table and create two cluster tables in the cluster_student cluster:

CREATE TABLE score(        sid   NUMBER(4),        sname VARCHAR2(10),        sscore  NUMBER(4) )CLUSTER  cluster_student(sid)

CREATE TABLE student1(       sid NUMBER(4),       sname VARCHAR2(10),       sage NUMBER(4))CLUSTER  cluster_student(sid)

Manage Clusters Create a Cluster Index

After you create a cluster Table, If you insert data directly, A ORA-02032 error is reported because no cluster index is created:

CREATE INDEX cluster_student_index ON CLUSTER cluster_student TABLESPACE learning;

Insert data:

INSERT INTO student1 VALUES(1000,'siege',24);

At this time, no error will occur.

Change cluster information

The created cluster can be changed as follows:

ALTER CLUSTER cluster_studentPCTFREE 60PCTUSED 50;

Delete cluster information

The syntax for deleting a cluster is the same as that for deleting a table. If the cluster contains a cluster Table, add the including tables clause.

DROP CLUSTER cluste_name [INCLUDING TABLES]