Oracle8 Clusters 22
C H A P T E R
3 3 3 3
In This Chapter
Oracle8 provides an additional method for storing Tables and
Indexes called clusters. An Oracle8 cluster is a Schema Object
containing a group of Tables which share one or more
common Columns (cluster Key), which are stored on the same
data block. Once Tables are created in clusters, their storage
format is transparent to the end User. You can access these
cluster Tables, and
Tables like any other nonclustered Table. Cluster Tables
should be predominantly used together in queries.
Tables and Indexes stored in a cluster have the following Guidelines in
immediate advantages: creating clusters
3 Disk I/O and access times are reduced for queries Managing clusters
involving joins of clustered Tables.
3 The cluster Key value in a cluster is only stored once, on clusters
regardless of how many Rows in the cluster Tables
contain the Value. This condition can reduce the 3 3 3 3
physical Table storage requirements of a cluster Schema
Example Figure 22-1 illustrates the comparison of storing the STUDENT
and DEPARTMENT Tables in a clustered and nonclustered
format. The cluster Key is the D_NO. The right side of the
diagram shows a typical, nonclustered format. The STUDENTS
Table and the DEPARTMENT Tables each reside in separate data
blocks. The left side of the diagram shows how clustered
Tables are stored. The values in the two related tables are
merged into a single table structure and sorted by the cluster
Key (D_NO, D_NAME, ID). All merged data is stored together in
one area in the database.
556 Chapter 22 3 Oracle8 Clusters
DEPARTMENT / STUDENT CLUSTER
D_NO D_NAME ID FIRST_NAME LAST_NAME
104 Acct 22 John Smith
ID FIRST_NAME LAST_NAME D_NO
Acct 56 Lani Kaupo
11 Patricia Butler 340
Acct 47 Fred Savage
22 John Smith 104
340 Engr 44 Amy Johnson
83 Laurel Watson 340
Engr 11 Patricia Butler
44 Amy Johnson 340
Engr 83 Laurel Watson
47 Fred Savage 104
56 Lani Kaupo 104
Figure 22-1: Cluster and noncluster Table formats
Creating clusters, cluster Tables, and cluster Indexes
The following Objects comprise a cluster:
3 The cluster Schema Object
3 The cluster Table
3 The cluster Index
Creating cluster Schema Objects
To create a cluster in your Schema, you must have the CREATE CLUSTER system
privilege. To create a cluster in another Schema, you must use the CREATE ANY
Chapter 22 3 Clusters 557
CLUSTER system privilege. Since the cluster requires storage space, you must also
have a quota for the Tablespace intended to contain the cluster or have the
UNLIMITED TABLESPACE system privilege.
Reference CREATE CLUSTER
Create a cluster by executing the SQL CREATE CLUSTER command with the
SQL*Plus or Oracle SQL Worksheet Oracle8 tools. The following syntax creates a
CREATE CLUSTER Cluster Name
A cluster can also be created using the Oracle8 Schema Manager. Figure 22-2
introduces the Schema Manager Create Cluster dialog box. Enter general
information about the cluster, such as the cluster’s name and the creation
Tablespace location, in this dialog box.
Figure 22-2: Create clusters using the
Oracle8 Schema Manager.
558 Chapter 22 3 Oracle8 Clusters
Figure 22-3 illustrates the addition of CREATE CLUSTER parameters via the Oracle8
Schema Manager Create Cluster interface.
Figure 22-3: Specify cluster parameters
by clicking the Storage tab in the Cluster
The following list describes the CREATE CLUSTER syntax parameters:
3 Cluster Name. The name of the cluster to be created.
3 Column_Name. The name of a Column in the cluster Key.
3 Datatype. The datatype of the cluster Key Column.
3 SIZE. The amount of byte space to store all rows with the same cluster Key
3 PCTFREE. The limit Oracle8 uses to determine when you can add additional
rows to a cluster’s data block.
3 PCTUSED. The space reserved in each cluster’s data blocks for future updates.
3 INITRANS. The initial number of concurrent update transactions allocated for
data blocks of the cluster. This value cannot be less than two or greater than
3 MAXTRANS. The maximum number of concurrent update transactions for any
given data block belonging to the cluster. This value cannot be less than the
value of INITRANS parameter or greater than 255. The default value is the
MAXTRANS value of the Tablespace containing the cluster.
3 TABLESPACE. The Tablespace in which the cluster is to be created.
3 STORAGE. The method of data blocks allocation to the cluster.
See For example, the following SQL statement uses the CREATE CLUSTER command
Reference to create the student_dept cluster:
Chapter 22 3 Clusters 559
CREATE CLUSTER student_dept (dept_id INTEGER)
STORAGE ( INITIAL 200k
Creating clustered Tables
To create a Table in a cluster, you must either have the CREATE TABLE or CREATE
ANY TABLE system privilege. The Tablespace quota or the UNLIMITED TABLESPACE
system privilege is not required to create a clustered Table. The Table is contained
in a cluster that already has allocated space.
A Table can be created in a cluster using the CREATE TABLE SQL command with the
CLUSTER option. Execute this command with SQL*Plus or Oracle SQL Worksheet.
Cluster Tables can also be created interactively using the Oracle8 Schema Manager.
Note The Table name must be unique within the User’s Schema.
For example, the following SQL statement creates the STUDENTS Table in the
CREATE TABLE STUDENTS
STUDENT_ID INTEGER NOT NULL,
STUDENT_FIRST_NAME VARCHAR2(25) NULL,
STUDENT_LAST_NAME VARCHAR2(25) NULL,
STUDENT_DEPT_ID INTEGER NULL,
STUDENT_ADDRESS VARCHAR2(50) NULL,
STUDENT_CITY VARCHAR2(25) NULL,
STUDENT_STATE VARCHAR2(15) NULL,
STUDENT_ZIP VARCHAR2(10) NULL,
CONSTRAINT PK_STUDENTS_1 PRIMARY KEY (STUDENT_ID)
CLUSTER student_dept (STUDENT_DEPT_ID);
The following SQL statement creates the DEPARTMENT Table in the student_dept
CREATE TABLE DEPARTMENT
DEPT_ID INTEGER NOT NULL,
DEPT_NAME VARCHAR2(25) NULL,
CONSTRAINT PK_DEPARTMENT1 PRIMARY KEY (DEPT_ID)
) CLUSTER student_dept (DEPT_ID);
560 Chapter 22 3 Oracle8 Clusters
Note A clustered Table can be created in a different Schema than the Schema containing
Creating cluster Indexes
A cluster Index must be created before you can load any data into a clustered
Table. To create a cluster Index, one of the following conditions must be true:
3 Your Schema contains the cluster and you have the CREATE INDEX system
3 You have the CREATE ANY INDEX system privilege.
In either case, you must also have a quota for the Tablespace intended to contain
the cluster Index or the UNLIMITED TABLESPACE system privilege.
Reference CREATE INDEX
Create a cluster Index in a cluster using the CREATE INDEX SQL command with the
For example, the following SQL statement creates the student_dept_index for the
CREATE INDEX student_dept_index
ON CLUSTER student_dept
STORAGE ( INITIAL 50k
Note When estimating the size of cluster Indexes, remember the Index is on each cluster
Key, not the actual rows; therefore, each Key only appears once in the Index.
Guidelines in creating clusters
Caution In comparison with storing Tables separately with their own Indexes, clusters can
reduce the performance of SQL DML statements (INSERT, UPDATE, and DELETE).
This reduction occurs because multiple cluster Tables share the same data block.
Thus, Oracle8 may require more blocks to store a clustered Table than a
nonclustered Table. This storing method can increase the space required to hold the
clustered Tables and the number of blocks that it must traverse for a SQL query.
Chapter 22 3 Heading 1 561
The following sections discuss the following guidelines to help you determine
which tables you should cluster:
3 Selecting candidate cluster Tables
3 Identifying the cluster Key
3 Optimizing the cluster Index
3 Using the PCTFREE and PCTUSED parameters
3 Using the SIZE parameter
3 Using the TABLESPACE parameter
Selecting candidate cluster Tables
To identify candidate Tables that should be created in a cluster, look for Tables:
1. Related via referential integrity Constraints
2. Joined together in SELECT statements
Oracle8 stores all rows sharing the same cluster Key in the same data block. Thus,
clustering Tables based on Columns used in joins reduces the number of data
blocks you must scan to process the query — all rows needed for a join on a cluster
Key are in the same data block. This reduction improves the performance on join
Single Tables that you do not access via SQL joins can also be good candidates for
creation in a cluster. This creation is advantageous if a Table is queried
predominately by a particular Column. For example, if the STUDENTS Table is
queried department by department, the STUDENTS Table could be created in a
cluster where the cluster Key would be the dept_id.
Clusters do not affect application design because their format for storing Tables is
transparent to the end User. Regardless, only Tables predominately queried using
SQL SELECT statements that involve table joins should be created in clusters.
Therefore, application developers should be aware of Tables created in clusters to
monitor transactions types executed against the respective cluster Tables.
Identifying the cluster Key
The cluster Key is the Column or group of Columns that the cluster Tables have in
common. You specify the cluster Key Columns when creating the cluster and every
Table added to the cluster.
Every Table created in the cluster must have a Column that matches the size and
type of the Columns specified in the cluster Key. The actual Column names of each
Table do not have to match. The maximum number of Columns available in a
cluster Key is 16, with a maximum length of 239 bytes. Thus, cluster Keys cannot
include Columns that include the LONG or LONG RAW datatypes.
562 Chapter 22 3 Oracle8 Clusters
Cluster Keys should contain unique values allowing their respective Rows to fill
approximately one data block. If you choose cluster Keys incorrectly, only a few
rows per cluster Key are stored in a data block. This situation wastes space and
causes queries to scan unnecessary data blocks.
Data contained in cluster Tables can be inserted, updated, and deleted. The
placement of data depends on the cluster Key, however. Changing the cluster Key of
a row in an UPDATE operation may cause Oracle8 to physically relocate the row.
Therefore, frequently updated Columns are not good candidates for the cluster Key.
Optimizing the cluster Index
You must create a cluster Index before you can execute any SQL DML statements
against any clustered Tables. Thus, clustered Tables cannot be populated without a
A cluster Index must be created on the cluster Key Columns after you have created
a cluster. Defined specifically for clusters, a cluster Index contains an entry for each
cluster Key value. Use the cluster Index to point to the data block that contains a
cluster row based on a cluster Key value.
You can place cluster Indexes in clusters that are not Indexed.
Note Oracle assesses a given row with a minimum of two I/Os, depending on the number
of levels traversed in the Index. Cluster Indexes differ from nonclustered Indexes in
the following ways:
3 They can contain Keys with a NULL value.
3 The cluster Indexes point to the first data block in the chain for a given
cluster Key value.
3 A cluster Index contains one entry per cluster Key value, rather than one
entry per cluster row.
Caution If you drop a cluster Index, data in the cluster remains but is inaccessible. Oracle8
only permits access to the cluster data after another cluster Index is created based
on the cluster Key values.
Suppose you want to move an Index to another Tablespace or alter its storage
requirements. These actions require you to drop and re-create a cluster Index. The
cluster’s data is not available after you drop the cluster Index, but becomes
available again after you re-create the cluster Index.
Using the PCTFREE and PCTUSED parameters
Use the PCTFREE and PCTUSED parameters specified in the CREATE CLUSTER
command to optimize space utilization. These parameters also optimize the
amount of space reserved for updates to the current rows in a data block of a
cluster’s data segment.
Chapter 22 3 Clusters 563
Note PCTFREE and PCTUSED parameters set for Tables created in a cluster are ignored
because cluster Tables automatically use the settings for the cluster in which they
Using the SIZE parameter
The SIZE parameter in the CREATE CLUSTER command specifies the average
amount of space required to store all rows for a cluster Key value.
Use the SIZE parameter to optimize the number of cluster Keys that can fit in a
cluster data block. This optimization maximizes the storage efficiency of Keys
within a cluster.
The maximum number of cluster Key values per data block is fixed by the SIZE
parameter. Actually, Oracle8 does not reserve space for each cluster Key value or
guarantee the number of cluster Keys you can assign to a block.
Example For example, if each data block has 2100 bytes of available space and the specified
cluster Key size is 600 bytes, each data block can potentially hold rows for three
cluster Keys. If the SIZE parameter is greater than the amount of available space
per data block, each data block contains Rows for only one cluster Key value.
If the cluster Key rows grow and no longer fit in one data block, the cluster Key data
rows flow onto other data blocks in the cluster. This overflow causes the data blocks
to chain together. The cluster Index points to the beginning of the chain of the data
blocks, each of which contains the cluster Key value and the associated rows. This
chaining action speeds data access to all values within the given cluster Key.
For example, if the SIZE parameter determines that four cluster Key values are
allowed per data block, this limitation does not prevent rows for one cluster Key
Value from taking up all available space in the block. If more rows exist for a given
Key than can fit in a single block, the block is chained.
Note A cluster Key value is only stored once in a data block.
Using the TABLESPACE parameter
Use the TABLESPACE parameter in the CREATE CLUSTER or CREATE INDEX
statements to identify the Tablespace in which you wish to store the new cluster or
Index. Specifying a Tablespace allows the cluster and Index Schema Objects to be
created in different Tablespaces. If a cluster and its respective Index are created on
two different Tablespaces stored on different physical devices, you can retrieve the
cluster data and Index data simultaneously.
564 Chapter 22 3 Oracle8 Clusters
Managing cluster Schema Objects
Managing a cluster involves the modification and deletion of the following Oracle8
3 Cluster Tables
3 Cluster Indexes
You can manage cluster Schema Objects using the Oracle Schema Manager or by
executing the code in the following section using SQL*Plus or Oracle SQL
One of the following conditions must exist to alter a cluster: you must have the
ALTER ANY CLUSTER system privilege or the cluster must exist in your Schema.
Reference ALTER CLUSTER
To alter a cluster, use the ALTER CLUSTER command. The syntax follows:
ALTER CLUSTER Cluster Name
Only the following cluster parameters can be altered using the ALTER CLUSTER
3 The PCTFREE and PCTUSED data block space usage parameters
3 The average cluster Key SIZE parameter
3 The INTRANS and MAXTRANS transaction entry settings
3 The storage parameters
If you alter the PCTFREE and PCTUSED data block usage parameters or the cluster
SIZE parameter, this alteration applies to all currently allocated and subsequently
assigned data blocks used by the cluster.
Chapter 22 3 Heading 1 565
Note Oracle8 recognizes altering data blocks already allocated for the cluster on an as
needed basis, not immediately!
When you alter the INITRANS or MAXTRANS transaction entry parameters of a
cluster, the new setting for INITRANS applies only to the data blocks subsequently
allocated for a cluster. Alternately, a new MAXTRANS value applies to all presently
and subsequently allocated blocks for a cluster.
The storage parameters INITIAL and MINEXTENTS cannot be altered. All new
settings for the other storage parameters only affect extents subsequently allocated
for the cluster.
For example, the following SQL statement alters the PCTFREE and PCTUSED
parameters for the student_dept cluster:
ALTER CLUSTER student_dept
Altering clustered Tables
One of the following conditions must exist to alter a clustered Table: you must have
the ALTER ANY TABLE system privilege or the clustered Table must exist in your
Use the ALTER TABLE command to alter clustered Tables.
Only the following SQL operations can be executed with the ALTER TABLE
command for clustered Tables:
3 Create or modify cluster Table Columns
3 Create, drop, enable, or disable integrity Constraints or Triggers for a
You cannot use data block-related parameters, transaction entry parameters, or
storage parameters in conjunction with the ALTER TABLE statement for a clustered
Table. Oracle8 uses the parameters of the cluster to apply to all clustered Tables.
Use the standard ALTER INDEX SQL command to alter cluster Indexes.
One of the following conditions must exist to drop a cluster: you must have the
DROP ANY CLUSTER system privilege or the cluster must exist in your Schema. The
following syntax drops a cluster:
DROP CLUSTER Cluster Name
566 Chapter 22 3 Oracle8 Clusters
For example, the following SQL statement drops the student_dept cluster:
DROP CLUSTER student_dept;
When a cluster is dropped, the following Schema Objects are also dropped:
3 Tables within the respective cluster
3 The corresponding cluster Index
All extents belonging to both the cluster’s data segment and Index segment are
returned to the containing Tablespace. They immediately become available for
other segments within the Tablespace to occupy.
Dropping clustered Tables
One of the following conditions must exist to drop a clustered Table: your Schema
must contain the cluster or you must have the DROP ANY TABLE system privilege.
Clustered Tables can be dropped individually without affecting the actual cluster,
other clustered Tables, or even cluster Indexes.
Note A clustered Table is dropped like a nonclustered Table by using the DROP TABLE
For example, the following syntax drops the STUDENTS Table from the
DROP TABLE students;
While dropping a clustered Table, Oracle8 deletes each row individually. To avoid
this time-consuming task, drop the entire cluster with the INCLUDING TABLES
option. For example:
DROP CLUSTER student_dept INCLUDING TABLES;
Note If you do not use the INCLUDING TABLES option when the cluster contains Tables,
Oracle8 returns an error message to the end User.
Dropping cluster Indexes
A cluster Index can be dropped without affecting the cluster or its clustered Tables.
Dropping a cluster Index immediately marks the cluster as inaccessible. To enable
end Users to access the cluster, You must re-create the cluster Index. Cluster
Indexes are dropped as part of a Procedure to rebuild the fragmented cluster
To drop a cluster without Tables and its respective cluster Index, use the SQL
command DROP CLUSTER. For example:
DROP CLUSTER student_dept;
Chapter 22 3 Clusters 567
If one or more Tables in a cluster contain Primary or Unique Keys referenced by
Foreign Key Constraints of Tables outside the cluster, the cluster cannot be
dropped unless the dependent FOREIGN KEY Constraints are dropped first.
Remove FOREIGN KEY dependencies by using the following CASCADE CONSTRAINTS
option of the DROP CLUSTER command:
DROP CLUSTER student_dept INCLUDING TABLE CASCADE CONSTRAINTS
Gathering information on clusters
Use the following Data Dictionary views to gather statistics on clusters in your
You can also use the ANALYZE command to collect statistics about clusters. The
cluster must be in your own Schema or you must have the ANALYZE ANY system
privilege to use the ANALYZE command.
Use the ANALYZE command to:
3 Collect statistics about clusters used by the Optimizer and store the results in
the Oracle8 Data Dictionary.
3 Validate the structure of the cluster Schema Object.
3 Identify migrated and chained rows of a cluster into a Table local to your
The general syntax for using the ANALYZE command follows:
ANALYZE CLUSTER Cluster Name
|VALIDATE REF UPDATE
|LIST Chained ROW INTO Table Name}
For example, the following statement computes statistics for the STUDENTS Table
and its respective Indexes in the cluster STUDENT_DEPT:
568 Chapter 22 3 Oracle8 Clusters
ANALYZE TABLE STUDENTS
View the actual results of this operation using the USER_CLUSTERS and
USER_CLU_COLUMNS Data Dictionary views.
Striving to optimize storage allocation for Tables, Oracle8 provides technique of
clustering to the DBA . An Oracle8 cluster can be thought of as a container that
stores Tables sharing the same Primary Key. Clustered Tables, are stored on the
same data block, with their respective storage format transparent to the User.
The following key points summarize this chapter:
3 Oracle8 can cluster Tables and their respective Indexes.
3 Two types of Table clustering techniques can be implemented: Clusters based
on a cluster Key (Primary Key) and clusters based on a hash function.
3 Cluster Keys are only stored once.
3 Clustering, if implemented correctly, can reduce disk I/O for queries that
involve complex joins.
3 A cluster index must be created before any data can be loaded into any
3 Only use hash clusters on Tables where the queries use equality operators
and the Tables are relatively static in size.
3 Upon creation, the storage parameters can be used to optimize the storage
allocation and performance of the clusters.
3 3 3