Creating Clusters

• Creating Clustered Tables

• Creating Cluster Indexes

To create a cluster in another user's schema, you must have the CREATE ANY CLUSTER system privilege and the owner must have a quota for the tablespace intended to contain the cluster or the UNLIMITED TABLESPACE system privilege.

You can create a cluster using the SQL command CREATE CLUSTER. The following statement creates a cluster named EMP_DEPT, which stores the EMP and DEPT tables, clustered by the DEPTNO column:

CREATE CLUSTER emp_dept (deptno NUMBER(3))

   PCTUSED 80

   PCTFREE 5

   SIZE 600

   TABLESPACE users

   STORAGE (INITIAL 200k

      NEXT 300K

      MINEXTENTS 2

      MAXEXTENTS 20

      PCTINCREASE 33);

Creating Clustered Tables

You can create a table in a cluster using the SQL command CREATE TABLE with the CLUSTER option. The EMP and DEPT tables can be created in the EMP_DEPT cluster using the following statements:

CREATE TABLE dept (

   deptno NUMBER(3) PRIMARY KEY, . . . )

   CLUSTER emp_dept (deptno);

CREATE TABLE emp (

   empno NUMBER(5) PRIMARY KEY,

   ename VARCHAR2(15) NOT NULL,

   . . .

   deptno NUMBER(3) REFERENCES dept)

   CLUSTER emp_dept (deptno); 

Note: You can specify the schema for a clustered table in the CREATE TABLE statement; a clustered table can be in a different schema than the schema containing the cluster.

Creating Cluster Indexes

• Your schema contains the cluster and you have the CREATE INDEX system privilege.

• You have the CREATE ANY INDEX system privilege.

A cluster index must be created before any rows can be inserted into any clustered table. The following statement creates a cluster index for the EMP_DEPT cluster:

CREATE INDEX emp_dept_index

   ON CLUSTER emp_dept

   INITRANS 2

   MAXTRANS 5

   TABLESPACE users

   STORAGE (INITIAL 50K

      NEXT 50K

      MINEXTENTS 2

      MAXEXTENTS 10

      PCTINCREASE 33)

   PCTFREE 5;

The cluster key establishes the relationship of the tables in the cluster. Several storage settings are explicitly specified for the cluster and cluster index.

See Also: See Chapter 20 for more information about system privileges, and Chapter 19 for information about tablespace quotas.