Create Cluster Tables

View All Scripts Login to Run Script

Script Name Create Cluster Tables
Description Creates cluster, cluster tables, and index cluster. Queries the data dictionary to display object information.
Area SQL General / SQL Query
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1
This statement creates a cluster named emp_dept, which stores the emp and dept tables, clustered by the deptno column. Note that the tablespace used is 'LIVESQL_USERS' and not 'User' in this case. Also note, even though the INDEX keyword is not used in this statement, an index cluster is created by default. However, if the user has the privileges to create indexes, they can create indexes manually before inserting records into the cluster.
Create cluster
```
CREATE CLUSTER emp_dept (deptno NUMBER(3))  
   SIZE 600  
   TABLESPACE LIVESQL_USERS 
   STORAGE (INITIAL 200K  
      NEXT 300K  
      MINEXTENTS 2  
      PCTINCREASE 33)
```
```
Statement processed.
```
Statement 2
This statement creates a cluster table named dept under emp_dept cluster. Note that the deptno column is commonly used with the emp_dept cluster and empl table.
Create cluster table
```
CREATE TABLE dept (  
   deptno NUMBER(3) PRIMARY KEY)  
   CLUSTER emp_dept (deptno)
```
```
Table created.
```
Statement 3
This statement creates another table named empl under emp_dept cluster. Note that the deptno column is commonly used with the emp_dept cluster and dept table.
Create cluster table
```
CREATE TABLE empl (  
   emplno NUMBER(5) PRIMARY KEY,  
   emplname VARCHAR2(15) NOT NULL,  
   deptno NUMBER(3) REFERENCES dept)  
   CLUSTER emp_dept (deptno)
```
```
Table created.
```

Statement 4

This statement creates an index for the emp_dept cluster. Note that the tablespace used is 'LIVESQL_USERS'.

Create index cluster

CREATE INDEX emp_dept_index 
   ON CLUSTER emp_dept 
   TABLESPACE LIVESQL_USERS 
   STORAGE (INITIAL 50K 
      NEXT 50K 
      MINEXTENTS 2 
      MAXEXTENTS 10 
      PCTINCREASE 33)

Index created.

Statement 5

This statement displays the cluster name, tablespace name, cluster type, and other storage parameters by querying the USER_CLUSTERS data dictionary view.

Query the USER_CLUSTER view

SELECT CLUSTER_NAME, TABLESPACE_NAME, CLUSTER_TYPE, PCT_INCREASE, MIN_EXTENTS, MAX_EXTENTS FROM USER_CLUSTERS


CLUSTER_NAME TABLESPACE_NAME CLUSTER_TYPE PCT_INCREASE MIN_EXTENTS MAX_EXTENTS
EMP_DEPT LIVESQL_USERS INDEX  - 1 2147483645

Statement 6
This statement displays the cluster related tables and cluster column, by querying the USER_CLU_COLUMNS data dictionary view.
Query the USER_CLU_COLUMNS
```
SELECT * FROM USER_CLU_COLUMNS
```
```
CLUSTER_NAME CLU_COLUMN_NAME TABLE_NAME TAB_COLUMN_NAME
EMP_DEPT DEPTNO DEPT DEPTNO
EMP_DEPT DEPTNO EMPL DEPTNO

2 rows selected.
```

CLUSTER_NAME	TABLESPACE_NAME	CLUSTER_TYPE	PCT_INCREASE	MIN_EXTENTS	MAX_EXTENTS
EMP_DEPT	LIVESQL_USERS	INDEX	-	1	2147483645

CLUSTER_NAME	CLU_COLUMN_NAME	TABLE_NAME	TAB_COLUMN_NAME
EMP_DEPT	DEPTNO	DEPT	DEPTNO
EMP_DEPT	DEPTNO	EMPL	DEPTNO

Statement 7

This statement queries USER_INDEXES to display the index attributes of the EMP_DEPT cluster.

Query the USER_INDEXES

SELECT INDEX_NAME, INDEX_TYPE, PCT_INCREASE, MIN_EXTENTS, MAX_EXTENTS FROM USER_INDEXES WHERE TABLE_NAME='EMP_DEPT'


INDEX_NAME INDEX_TYPE PCT_INCREASE MIN_EXTENTS MAX_EXTENTS
EMP_DEPT_INDEX CLUSTER  - 1 2147483645

INDEX_NAME	INDEX_TYPE	PCT_INCREASE	MIN_EXTENTS	MAX_EXTENTS
EMP_DEPT_INDEX	CLUSTER	-	1	2147483645

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database