CREATE CLUSTER

Syntax

physical_attributes_clause::=

storage_clause: See the "storage_clause".

parallel_clause::=

Purpose

To create a cluster. A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle stores together all the rows (from all the tables) that share the same cluster key.

For general information on clusters, see Oracle8i Concepts. For information on performance considerations of clusters, see Oracle8i Application Developer's Guide - Fundamentals. For suggestions on when to use clusters, see Oracle8i Designing and Tuning for Performance.

Prerequisites

To create a cluster in your own schema, you must have CREATE CLUSTER system privilege. To create a cluster in another user's schema, you must have CREATE ANY CLUSTER system privilege. Also, the owner of the schema to contain the cluster must have either space quota on the tablespace containing the cluster or UNLIMITED TABLESPACE system privilege.

Oracle does not automatically create an index for a cluster when the cluster is initially created. Data manipulation language (DML) statements cannot be issued against clustered tables until a cluster index has been created.

Keywords and Parameters

schema	is the schema to contain the cluster. If you omit schema, Oracle creates the cluster in your current schema.
cluster	is the name of the cluster to be created.
	After you create a cluster, you add tables to it. A cluster can contain a maximum of 32 tables. After you create a cluster and add tables to it, the cluster is transparent. You can access clustered tables with SQL statements just as you can nonclustered tables. For information on adding tables to a cluster, see "CREATE TABLE".
column	is the name of a column in the cluster key. You can specify up to 16 cluster key columns. These columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name. You cannot specify integrity constraints as part of the definition of a cluster key column. Instead, you can associate integrity constraints with the tables that belong to the cluster.
datatype	is the datatype of a cluster key column. For information on datatypes, see the section "Datatypes". Restrictions: You cannot specify a cluster key column of datatype LONG, LONG RAW, REF, nested table, varray, BLOB, CLOB, BFILE, or user-defined object type. You cannot use the HASH IS clause if any column datatype is not INTEGER or NUMBER with scale 0. You can specify a column of type ROWID, but Oracle does not guarantee that the values in such columns are valid rowids.
physical_attributes_clause	specifies the storage characteristics of the cluster. Each table in the cluster uses these storage characteristics as well.
PCTUSED	specifies the limit that Oracle uses to determine when additional rows can be added to a cluster's data block. The value of this parameter is expressed as a whole number and interpreted as a percentage.
PCTFREE	specifies the space reserved in each of the cluster's data blocks for future expansion. The value of the parameter is expressed as a whole number and interpreted as a percentage.
INITRANS	specifies the initial number of concurrent update transactions allocated for data blocks of the cluster. The value of this parameter for a cluster cannot be less than 2 or more than the value of the MAXTRANS parameter. The default value is 2 or the INITRANS value for the cluster's tablespace, whichever is greater.
MAXTRANS	specifies the maximum number of concurrent update transactions for any given data block belonging to the cluster. The value of this parameter cannot be less than the value of the INITRANS parameter. The maximum value of this parameter is 255. The default value is the MAXTRANS value for the tablespace to contain the cluster.
	For a complete description of the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters, see "CREATE TABLE".
storage_clause	specifies how data blocks are allocated to the cluster. See the "storage_clause".
SIZE	specifies the amount of space in bytes to store all rows with the same cluster key value or the same hash value. Use K or M to specify this space in kilobytes or megabytes. This space determines the maximum number of cluster or hash values stored in a data block. If SIZE is not a divisor of the data block size, Oracle uses the next largest divisor. If SIZE is larger than the data block size, Oracle uses the operating system block size, reserving at least one data block per cluster or hash value.
	Oracle also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. (This does not apply to hash clusters, because hash values are not actually stored in the cluster.) If you omit this parameter, Oracle reserves one data block for each cluster key value or hash value.
TABLESPACE	specifies the tablespace in which the cluster is created.
INDEX	creates an indexed cluster. In an indexed cluster, Oracle stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block, regardless of the number of tables and rows in which it occurs. After you create an indexed cluster, you must create an index on the cluster key before you can issue any data manipulation language (DML) statements against a table in the cluster. This index is called the cluster index. For information on creating a cluster index, see "CREATE INDEX".
	Note: You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key. If you specify neither INDEX nor HASHKEYS, Oracle creates an indexed cluster by default.
	See Also: Oracle8i Concepts for more information in indexed clusters.
HASHKEYS	creates a hash cluster and specifies the number of hash values for a hash cluster. In a hash cluster, Oracle stores together rows that have the same hash key value. The hash value for a row is the value returned by the cluster's hash function. Oracle rounds up the HASHKEYS value to the nearest prime number to obtain the actual number of hash values. The minimum value for this parameter is 2. If you omit both the INDEX clause and the HASHKEYS parameter, Oracle creates an indexed cluster by default. When you create a hash cluster, Oracle immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters. See Also: Oracle8i Concepts for more information on how Oracle allocates space for clusters.
	SINGLE TABLE	specifies that the cluster is a type of hash cluster containing only one table. This clause can provide faster access to rows than would result if the table were not part of a cluster. Restriction: Only one table can be present in the cluster at a time. However, you can drop the table and create a different table in the same cluster.
	HASH IS	specifies an expression to be used as the hash function for the hash cluster. The expression:
		Must evaluate to a positive value Must contain at least one column with referenced columns of any datatype as long as the entire expression evaluates to a number of scale 0. For example: NUM_COLUMN * length(VARCHAR2_COLUMN) Cannot reference user-defined PL/SQL functions
		Cannot reference SYSDATE, USERENV, TO_DATE, UID, USER, LEVEL, or ROWNUM Cannot evaluate to a constant Cannot contain a subquery Cannot contain columns qualified with a schema or object name (other than the cluster name)
	If you omit the HASH IS clause, Oracle uses an internal hash function for the hash cluster.
	The cluster key of a hash column can have one or more columns of any datatype. Hash clusters with composite cluster keys or cluster keys made up of noninteger columns must use the internal hash function.
parallel_clause	causes creation of the cluster to be parallelized. See also the Notes to the parallel_clause of "CREATE TABLE".
	NOPARALLEL	specifies serial execution. This is the default.
	PARALLEL	causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
	PARALLEL integer	specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
	Restriction: If the tables in cluster contain any columns of LOB or user-defined object type, this statement as well as subsequent INSERT, UPDATE, or DELETE operations on cluster are executed serially without notification.
CACHE	specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This clause is useful for small lookup tables.
NOCACHE	specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the default behavior.

Examples

Creating a Cluster

The following statement creates an indexed cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a cluster size of 512 bytes, and storage parameter values:

CREATE CLUSTER personnel 
    ( department_number  NUMBER(2) ) 
    SIZE 512 
    STORAGE (INITIAL 100K NEXT 50K); 

Adding Tables to a Cluster

The following statements add the EMP and DEPT tables to the cluster:

CREATE TABLE emp 
    (empno     NUMBER        PRIMARY KEY, 
     ename     VARCHAR2(10)  NOT NULL 
                             CHECK (ename = UPPER(ename)), 
     job       VARCHAR2(9), 
     mgr       NUMBER        REFERENCES scott.emp(empno), 
     hiredate  DATE          
        CHECK (hiredate < TO_DATE ('08-14-1998', 'MM-DD-YYYY')), 
     sal       NUMBER(10,2)  CHECK (sal > 500), 
     comm      NUMBER(9,0)   DEFAULT NULL, 
     deptno    NUMBER(2)      NOT NULL ) 
     CLUSTER personnel (deptno); 
 
CREATE TABLE dept 
    (deptno  NUMBER(2), 
     dname   VARCHAR2(9), 
     loc     VARCHAR2(9))
     CLUSTER personnel (deptno); 

Cluster Key Example

The following statement creates the cluster index on the cluster key of PERSONNEL:

CREATE INDEX idx_personnel ON CLUSTER personnel;
 

After creating the cluster index, you can insert rows into either the EMP or DEPT tables.

Hash Cluster Examples

The following statement creates a hash cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a maximum of 503 hash key values, each of which is allocated 512 bytes, and storage parameter values:

CREATE CLUSTER personnel
( department_number  NUMBER )
    SIZE 512  HASHKEYS 500 
    STORAGE (INITIAL 100K  NEXT 50K); 

Because the above statement omits the HASH IS clause, Oracle uses the internal hash function for the cluster.

The following statement creates a hash cluster named PERSONNEL with the cluster key made up of the columns HOME_AREA_CODE and HOME_PREFIX, and uses a SQL expression containing these columns for the hash function:

CREATE CLUSTER personnel 
    ( home_area_code  NUMBER,
      home_prefix     NUMBER ) 
    HASHKEYS 20
    HASH IS MOD(home_area_code + home_prefix, 101); 

Single-Table Hash Cluster Example

The following statement creates a single-table hash cluster named PERSONNEL with the cluster key DEPTNO and a maximum of 503 hash key values, each of which is allocated 512 bytes:

CREATE CLUSTER personnel
   (deptno NUMBER)
   SIZE 512 SINGLE TABLE HASHKEYS 500;