Database Logical Object Management _ Cluster

1. Cluster Concept:

is a set of tables that, if the SQL statement in your application often joins two or more tables, you can create them in a clustered way to improve performance. The tables in the cluster are stored in the same data block, and the same column values in each table are stored only one.

A cluster (Cluster) is a technique that stores a set of organically linked tables physically together and the values of the same key columns are stored in only one copy for improved processing efficiency.

2 Restrictions:

Each table in the L cluster must have a column that matches the size and type of the column specified in the cluster;

L The maximum number of available columns in the cluster code is 16, which means that a cluster has a maximum of 16 columns as the cluster code;

The maximum length of the L column is 239 bytes;

L Long and long RAW cannot be used as a cluster column code.

3 3 Building Clusters:

1) Create cluster syntax

CREATE CLUSTER CLUSTER

(column DATATYPE[,COLMN datatype] ...)

[Pctused 40|intger] [PCTFREE 10| Intger]

[SIZE Intger]

[Initrans 1|intger] [Maxtrans 255|intger]

[Tablespace tablespace]

[STORAGE STORAGE]

2) Steps to create a cluster and its tables:

Creating clusters with Create cluster

L Create a clustered index using CREATE index

L CREATE table and specify cluster with CREATE table

L Insert data and perform DML operations

4 Changing the cluster:

In the event that the user has the ALTER any CLUSTER permission, the established cluster (CLUSTER) can be changed to its settings, such as:

L Physical Properties: Pctfree,pctused,initrans,maxtrans and storage;

L STORE the general space capacity required for all rows for the cluster key value;

L default parallelism.

Command syntax:

ALTER CLUSTER Cluster_Name

{pctused integer

| PCTFREE integer

| SIZE integer

| Initrans integer

| Maxtrans integer

| STORAGE Cluase

}

5 Deleting a cluster:

All cluster can be deleted as long as the user has the drop any cluster permission.

The syntax is as follows:

DROP CLUSTER [user.] cluster [including TABLES]

6 Collecting Cluster Information:

Delete a cluster index: The index of a cluster can be deleted without affecting the table's data, but when the index of the cluster is deleted, the table belonging to that cluster becomes unavailable, so the index of the cluster must be established after the cluster's index is deleted. Sometimes in order to eliminate the fragmentation of disk space, we often do a delete cluster index operation.

L Dba_clu_columns or User_clu_columns

L Dba_clusters or User_cluster

1, Cluster
A collection of two or more tables with a public column
Data in a cluster table is stored in a public data block

2, cluster key
Unique identifier for rows in a cluster
Used to get rows

3, create cluster
You should first create the cluster and then create a table that makes up the cluster
Create CLUSTER statement for creating clusters
Advantages
Reduce disk I/O
Save disk space (the table's public columns are stored in the cluster)
Disadvantages
Slow performance when inserting data into a table
Tables with primary foreign key relationships, consider creating clusters, and putting public columns into clusters

4, a detailed example of creating clusters
--Create cluster data (create a public column into a cluster)
Create cluster Cluster_classno
(
Vclassno VARCHAR2 (10)
) tablespace users;

--Create a cluster index for the cluster key
CREATE index Idxclusterclassno on cluster cluster_classno;

--Create a cluster table (primary key table, foreign key table)
Create a primary key table-class table

CREATE TABLE Classes
(
Vclassno VARCAHR2 (10),
Vclassname VARCHAR2 (20)
) cluster Cluster_classno (VCLASSNO);

Create a foreign key table-Student table

CREATE TABLE Students
(
Vstudentno VARCHAR2 (10),
Vstudentname varchar2 (20),
Vclassno VARCHAR2 (10)
) cluster Cluster_classno (VCLASSNO);

--undo Cluster

Drop cluster cluster_classno including tables;


————————————————————————————————
————————————————————————————————
sql> Create cluster Cluster_classno
2 (
3 Vclassno varchar2 (10)
4) tablespace users;

The data families have been created.

Sql> CREATE index Idxclustclassno on cluster cluster_classno;

The index has been created.

Sql> CREATE TABLE Classes
2 (
3 Vclassno varchar2 (10),
4 vclassname varchar2 (20)
5) Cluster Cluster_classno (VCLASSNO);

The table is created.

Sql> CREATE TABLE Students
2 (
3 Vstudentno varchar2 (10),
4 vstudentname varchar2 (20),
5 Vclassno varchar2 (10)
6) Cluster Cluster_classno (VCLASSNO);

The table is created.

sql> drop cluster cluster_classno including tables;

The data cluster has been discarded.

Database Logical Object Management _ clusters