Oracle Clusters

 General Information
 Data Dictionary Objects
 System Privileges
 Clustering By Hash
 Clustering By Index
 Size Calculation

Create Cluster

• Create Simple Cluster With Cluster Index
• Create Simple Hash Cluster
• Sorted Hash Cluster
• Create Hash Cluster With Hash Expression
• Single Table Hash Cluster
• Create Index Cluster

Alter Cluster

• INITRANS
• MAXTRANS
• PCTFREE
• PCTUSED

Drop Cluster

• Drop Cluster

Data Dictionary Objects

• DBA
o dba_clusters
o dba_clu_columns
o dba_cluster_hash _expressions
o dba_all_tables
• ALL
o all_clusters
o all_clu_columns
o all_cluster_hash _expressions
o all_all_tables
• USER
o user_clusters
o user_clu_columns
o user_cluster_hash _expressions
o user_all_tables

System Privileges

o alter any cluster
o create any cluster
o create cluster
o drop any cluster

Clustering By Hash

Data is co-located within a single block based on a hash key and a hash function.

Clustering By Index
Data is co-located within a single block based on a common column index.

Size Calculation

Size of the primary key of the parent + ((size of the primary key of the children) * # of children) - ((size of the primary key of the parent) * # of children).



For example:

If the parent is the employee table where the primary key is ssn (VARCHAR2(9)) and the child is table is pay where the primary key is ssn (VARCHAR2(9)) and date of payment (date datatype which is 7 bytes) ... on average if there are 300 children records then 9 + (300 * (9+7)) - (300 * 9) or 9 + (300 * 7) = 2,109.

Create Cluster

Create Simple Cluster With Cluster Index

CREATE CLUSTER <schema_name>. <cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE<integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
<SINGLE TABLE>
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;

Create Simple Hash Cluster

CREATE CLUSTER <schema_name>.<cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE<integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
<SINGLE TABLE>
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;


Sorted Hash Cluster

CREATE CLUSTER <schema_name>.<cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE<integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
<SINGLE TABLE>
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;


Create Hash Cluster with Hash Expression

CREATE CLUSTER <cluster_name> (
column_name> <data_type>, <column_name> <data_type>
HASHKEYS <integer>
HASH IN <hash_expression>);

Single Table Hash Cluster

CREATE CLUSTER <cluster_name> (
column_name> <data_type>) SINGLE TABLE;

Create Index Cluster

CREATE CLUSTER <cluster_name>
(column_name> <data_type>);

This is very similar to creating a cluster created with cluster Index.

Alter Cluster

INITRANS

ALTER CLUSTER <cluster_name> INITRANS <integer>

MAXTRANS

ALTER CLUSTER <cluster_name> MAXTRANS <integer>

PCTFREE
ALTER CLUSTER <cluster_name> PCTFREE <integer>

PCTUSED

ALTER CLUSTER <cluster_name> PCTUSED <integer>

Drop Cluster
DROP CLUSTER <cluster_name>;

Note:
The word cluster in Oracle bears not relationship to its use in SQL Server and other RDBMS products.

__________________
-Murali..

Hi,

How to create the Multi-Table Cluster by Hash and Multi-Table Cluster by Index

-R.Gopi

Here it is,

Multi-Table Cluster by Hash

CREATE CLUSTER <schema_name>.<cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE<integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
<SINGLE TABLE>
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;

Multi-Table Cluster by Index


CREATE CLUSTER <schema_name>. <cluster_name> (
<cluster_key_column_name> <data_type> <SORT>)
PCTFREE<integer>
PCTUSED <integer>
INITRANS <integer>
MAXTRANS <integer>
SIZE <integer><K | M | G | T>
TABLESPACE <tablespace_name>
INDEX
<SINGLE TABLE>
HASHKEYS <integer>
HASH IS <expression>
PARALLEL <integer>
<NOWROWDEPENDENCIES | ROWDEPENDENCIES>
<CACHE | NOCACHE>;

__________________
-Murali..