Oracle – What is cluster? How to create cluster?

What is a cluster?
It is an object in schema that contains data from one or more tables having one or more common columns.

Oracle Cluster

What do we create cluster?
It helps in:
1. Reducing the Disk I/O.
2. Improving the Cluster Table Joins access time.

What is a cluster key?
It is a one or more common column between clustered tables. We specify the cluster key columns while creating the cluster.

How to create cluster?

-- Query To Create Cluster
 CREATE CLUSTER TEST_CLUSTER
 (DEPTNO NUMBER(2));
-- Query To Check Cluster
SELECT CLUSTER_NAME, CLUSTER_TYPE, HASHKEYS, SINGLE_TABLE FROM USER_CLUSTERS;
-- Output of above query
CLUSTER_NAME       CLUST HASHKEYS   SINGL
------------------ ----- ---------- -----
TEST_CLUSTER       INDEX 0          N
-- Query to Create Index on Cluster:
CREATE INDEX IDX_TEST_CLUSTER
ON CLUSTER TEST_CLUSTER;

-- Query To Check the Clustered Index
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE FROM USER_INDEXES;
-- Output of above query
INDEX_NAME          INDEX_TYPE    TABLE_OWNER  TABLE_NAME     TABLE_TYPE
-----------------   ------------- -----------  -------------- -----------
IDX_TEST_CLUSTER    CLUSTER       ATUL         TEST_CLUSTER   CLUSTER
-- Query To Create DEPT_ID_10 Table for Department Id 10 from
-- EMP table and add them to above created TEST_CLUSTER Cluster
CREATE TABLE DEPT_ID_10
CLUSTER TEST_CLUSTER ( DEPTNO )
AS
   SELECT   *
     FROM   EMP
    WHERE   DEPTNO = 10;
-- Query To Check data from DEPT_ID_10 table
SELECT * FROM DEPT_ID_10;
-- Output of above query
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
-- Query To Create DEPT_ID_20 Table for Department Id 20 from
-- EMP table and add them to above created TEST_CLUSTER Cluster
CREATE TABLE DEPT_ID_20
CLUSTER TEST_CLUSTER ( DEPTNO )
AS
   SELECT   *
     FROM   EMP
    WHERE   DEPTNO = 20;
-- Query To Check data from DEPT_ID_20 table
SELECT * FROM DEPT_ID_20;
-- Output of above query
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

What is a hash cluster?
Oracle database uses hash function to generate numeric hash values based on specific cluster key values which can be either single column or composite one.
Oracle database uses this hash function to the cluster key value of row to find or store a row in hash cluster.

How to create hash cluster?

-- Query To Create Hash Cluster
CREATE CLUSTER TEST_HASHCLUSTER (ENAME VARCHAR2(10))
HASHKEYS 10;

-- Query To Create Hash Cluster using SQL Expression
CREATE CLUSTER TEST_SALHASHCLUSTER
 (LOSAL NUMBER, HISAL NUMBER)
 HASHKEYS 100
 HASH IS LOSAL + HISAL;

How to create single table hash cluster?
We can also create single table hash cluster. However, we have to make sure that only single table exists in it.

-- Query To Create Single Table Hash Cluster
CREATE CLUSTER TEST_DEPTCLUSTER (DEPTNO NUMBER(2))
 SINGLE TABLE HASHKEYS 100;

-- Query To Check all of the above created Clusters
SELECT CLUSTER_NAME, CLUSTER_TYPE, HASHKEYS, SINGLE_TABLE FROM USER_CLUSTERS;
-- Output of above query
CLUSTER_NAME                   CLUST   HASHKEYS SINGL
------------------------------ ----- ---------- -----
TEST_SALHASHCLUSTER            HASH         101     N
TEST_HASHCLUSTER               HASH          11     N
TEST_DEPTCLUSTER               HASH         101     Y
TEST_CLUSTER                   INDEX          0     N

How to drop all of the above clusters?

-- Query To dROP Cluster
DROP CLUSTER TEST_SALHASHCLUSTER INCLUDING TABLES;
DROP CLUSTER TEST_HASHCLUSTER INCLUDING TABLES;
DROP CLUSTER TEST_DEPTCLUSTER INCLUDING TABLES;
DROP CLUSTER TEST_CLUSTER INCLUDING TABLES;

If you have any comments then please add it below.  Feedbacks are always welcome

Did you like the above post?

View Results

Loading ... Loading ...
Summary
Oracle - What is cluster? How to create cluster?
Article Name
Oracle - What is cluster? How to create cluster?
Description
It is an object in schema that contains data from one or more tables having one or more common columns.
Author
Publisher Name
Atul
Publisher Logo

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.