Oracle Clusters

A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database.
Clustering provides more control over the physical storage of rows within the database. Clustering can reduce both the time it takes to access clustered tables and the space needed to store the table. 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.
If you cannot fit all rows for one hash value into a data block, do not use hash clusters. Performance is very poor in this circumstance because an insert or update of a row in a hash cluster with a size exceeding the data block size fills the block and performs row chaining to contain the rest of the row.
Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values. Before clustering, consider its benefits and trade-offs in light of the operations you plan to perform on your data. For more information on the performance implications of clustering, see Oracle8 Tuning.
Indexed Clusters
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. This saves disk space and improves performance for many operations.
You may want to use indexed clusters in the following cases:
Your queries retrieve rows over a range of cluster key values.
Your clustered tables may grow unpredictably.
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. As with the columns of any index, the order of the columns in the cluster key affects the structure of the cluster index.
A cluster index provides quick access to rows within a cluster based on the cluster key. If you issue a SQL statement that searches for a row in the cluster based on its cluster key value, Oracle searches the cluster index for the cluster key value and then locates the row in the cluster based on its ROWID.
Example I
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 PCTINCREASE 10);
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 >= SYSDATE),
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);
The following statement creates the cluster index on the cluster key of PERSONNEL:
CREATE INDEX idx_personnel ON CLUSTER personnel;