Detailed Explanation of the Use of Oracle Cluster

  • 2021-09-05 01:14:51
  • OfStack

Cluster is actually a group of tables, which is composed of a group of multiple tables sharing the same data block. Combining the tables that are often used in one cluster can improve the processing efficiency; A table in a cluster is called a cluster table.
The order of establishment is: cluster → cluster table → cluster index → data
Format for creating clusters
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 number of bytes required for the estimated average cluster key and the rows associated with it.
1. Create a cluster

    create cluster my_clu (deptno number )  
    pctused 60  
    pctfree 10  
    size 1024  
    tablespace users  
    storage (  
    initial 128 k  
    next 128 k  
    minextents 2  
    maxextents 20  
    );  

2. Create a cluster table

    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

create index clu_index on cluster my_clu;

Note: If the cluster index is not created, an error will be reported when inserting data: ORA-02032: clustered tables cannot be used before the cluster index is built
Management cluster
Modify cluster properties using ALTER (you must have permissions on ALTER ANY CLUSTER)
1. Modify cluster attributes
Cluster properties that can be modified include:
* PCTFREE, PCTUSED, INITRANS, MAXTRANS, STORAGE
* Average of space required for all rows to store cluster key values SIZE
* Default parallelism
Note:
* The values of INITIAL and MINEXTENTS cannot be modified
* PCTFREE, PCTUSED and SIZE parameters are modified to apply to all data blocks
* INITRANS, MAXTRANS only for later allocated blocks
* Modifications to the STORAGE parameter only affect the disks that are later allocated to the cluster
Example:

    alter cluster my_clu  
    pctused 40  

2. Delete the cluster

    drop cluster my_clu; --  Applicable only to delete 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: Cluster table can be deleted like ordinary table 1.
3. Empty the cluster

truncate cluster my_clu;

Note: All tables on this cluster are emptied
Hash clustering table
In the cluster table, Oracle uses the key values stored in the index to locate the rows in the table, while in the hash cluster table, hash function is used instead of cluster index. First, hash calculation is carried out by internal function or custom function, and then the calculated code value is used to locate the rows in the table. Creating a hash cluster requires the HASHKEYS clause.
1. Create a hash cluster

    create cluster my_clu_two(empno number(10) )  
    pctused 70  
    pctfree 10  
    tablespace users  
    hash is empno  
    hashkeys 150 ;  

Description:
* The hash is clause specifies the column to be hashed, which can be specified as a hash value if the column is a 1-only label row
* hashkeys specifies and limits the number of 1-only hash values that a hash function can produce
2. Create a hash table

    create table t2_emp (  
    empno number ( 10 ),  
    ename varchar2 ( 20 ),  
    birth_date date ,  
    deptno number )  
    cluster my_clu_two(empno);  

Note:
* You must set the precision of the numeric value
* Hash clusters cannot and do not need to be indexed
* Hash cluster cannot ALTER: size, hashkeys, hash is parameters
The situation that cluster table is not suitable
1) Clustered tables can have a negative impact on the performance of the DML if a large number of modifications to the tables in the cluster are expected
2) A full-table scan of a single table is very unsuitable because it can only cause full-table scans of other tables
3) TRUNCATE and load tables frequently, because tables in clusters cannot be TRUNCATE, only TRUNCATE clusters
4) Do not cluster tables if they are only occasionally joined or their common columns are frequently modified
5) Do not cluster tables if more than one or two Oracle blocks are frequently queried from all tables with the same clustering key value
6) Do not use clustering if there is not enough space and additional space cannot be allocated for new records to be inserted

Related articles: