Use of Oracle Clusters

A cluster is actually a group of tables, which are composed of multiple tables that share the same data block. Combining tables that are frequently used together into clusters can improve processing efficiency; a table in a cluster is called a cluster table.
The creation sequence is: Cluster → cluster Table → Cluster Index → data
Cluster creation format
Create cluster cluster_name
(Column date_type [, column datatype]...)
[PCTUSED 40 | integer] [PCTFREE 10 | integer]
[SIZE integer]
[INITRANS 1 | integer] [MAXTRANS 255 | integer]
[TABLESPACE tablespace]
[STORAGE storage]
SIZE: Specifies the average cluster key and the number of bytes required for the related rows.
1. Create a cluster
Copy codeThe Code is as follows:
Create cluster my_clu (deptno number)
Pctused 60
Pctfree 10
Size 1024
Tablespace users
Storage (
Initialize 128 k
Next 128 k
Minextents 2
Maxextents 20
);

2. Create a cluster Table
Copy codeThe Code is as follows:
Create table t1_dept (
Deptno number,
Dname varchar2 (20)
)
Cluster my_clu (deptno );
Create table t1_emp (
Empno number,
Ename varchar2 (20 ),
Birth_date date,
Deptno number
)
Cluster my_clu (deptno );

3. Create an index for the cluster
Copy codeThe Code is as follows:
Create index clu_index on cluster my_clu;

Note:If no cluster index is created, an error is reported during data insertion: ORA-02032: clustered tables cannot be used before the cluster index is built
Manage Clusters
Use ALTER to modify CLUSTER attributes (you must have the alter any cluster permission)
1. Modify cluster attributes
Attributes of a cluster that can be modified include:
* PCTFREE, PCTUSED, INITRANS, MAXTRANS, and STORAGE
* To store the average SIZE of space required for all rows of the cluster key value
* Default degree of Parallelism
Note:
* INITIAL and MINEXTENTS values cannot be modified.
* The modified PCTFREE, PCTUSED, and SIZE parameters apply to all data blocks.
* INITRANS and MAXTRANS are only applicable to data blocks allocated in the future.
* After the STORAGE parameter is modified, only the disks allocated to the cluster will be affected.
Example:
Copy codeThe Code is as follows:
Alter cluster my_clu
Pctused 40

2. delete a cluster
Copy codeThe Code is as follows:
Drop cluster my_clu; -- applicable only to deleting empty Clusters
Drop cluster my_clu including tables; -- delete clusters and cluster tables
Drop cluster my_clu including tables cascade constraints; -- delete foreign key constraints at the same time

Note:A cluster table can be deleted like a normal table.
3. Clear the Cluster
Copy codeThe Code is as follows:
Truncate cluster my_clu;

Note:All the table data in this cluster is cleared.
Hash clustering table
In a cluster Table, Oracle uses the key values stored in the index to locate the rows in the table. In a hash cluster Table, the hash function replaces the Cluster Index, hash calculation is performed by using internal functions or custom functions, and then the calculated code value is used to locate rows in the table. The HASHKEYS clause is required to create a hashed cluster.
1. Create a hashed Cluster
Copy codeThe Code is as follows:
Create cluster my_clu_two (empno number (10 ))
Pctused 70
Pctfree 10
Tablespace users
Hash is empno
Hashkeys 150;

Note:
* The hash is clause specifies the columns to be hashed. If the column is a unique row, you can specify the column as the hash value.
* Hashkeys specifies and limits the number of unique hash values that a hash function can generate.
2. Create a hash
Copy codeThe Code is as follows:
Create table t2_emp (
Empno number (10 ),
Ename varchar2 (20 ),
Birth_date date,
Deptno number)
Cluster my_clu_two (empno );

Note:
* The precision of the value must be set.
* You cannot create indexes for hashed clusters.
* The hash clustering cannot be ALTER: size, hashkeys, or hash is parameters.
Clustering tables are not suitable.
1) if a large number of tables in the cluster are expected to be modified, the cluster Table will have a negative impact on DML performance.
2) It is not suitable for full table scanning for a single table, because it can only cause full table scanning for other tables.
3) perform TRUNCATE and loading on tables frequently, because tables in clustering cannot be TRUNCATE and can only be TRUNCATE clusters.
4) if the table is occasionally connected or its public columns are often modified, do not cluster the table.
5) if the number of data results frequently queried from all tables with the same cluster key value exceeds one or two Oracle blocks
6) If there is not enough space and no additional space can be allocated for the new record to be inserted, do not use clustering.