Cluster

• Store tables related to each other
• Cluster Key - how tables are joined
• Can't use 'Tablespace' or 'Storage' clause when creating tables

------------------------------------------------------------------------
-- Drop all tables before dropping constraint
------------------------------------------------------------------------
Drop Table Emp Cascade Constraints;
Drop Table Dept Cascade Constraints;
Drop Index EmpAndDeptIdx ;
Drop Cluster EmpAndDept;
------------------------------------------------------------------------
-- The column name is irrelevant; the type should match the key type and length.
------------------------------------------------------------------------
Create Cluster EmpAndDept (ClusterKey Number(2));

------------------------------------------------------------------------
-- The Index must be created before insterting rows.
-- Key is stored once for both tables.
------------------------------------------------------------------------
Create Index EmpAndDeptIdx on Cluster EmpAndDept;

------------------------------------------------------------------------
-- Create the Dept Table and show it as being part of the cluster.
------------------------------------------------------------------------
Create Table Dept
(
 Deptno              Number(2) Not Null,
 Dname               Varchar2(14),
 Loc                 Varchar2(13),
 Constraint Dept_Deptno_Pk Primary Key (Deptno)
)
Cluster EmpAndDept(Deptno);

Insert Into Dept Values (10,'ACCOUNTING','NEW YORK');
Insert Into Dept Values (20,'RESEARCH','DALLAS');
Insert Into Dept Values (30,'SALES','CHICAGO');
Insert Into Dept Values (40,'OPERATIONS','BOSTON');

------------------------------------------------------------------------
-- Create the Emp Table and show it as being part of the cluster.
------------------------------------------------------------------------
Create Table Emp
(
 Empno               Number(4) Not Null,
 Ename               Varchar2(10),
 Job                 Varchar2(9),
 Mgr                 Number(4) ,
 Hiredate            Date,
 Sal                 Number(7,2),
 Comm                Number(7,2),
 Deptno              Number(2) Not Null,
 Constraint Emp_Deptno_Fk Foreign Key (Deptno) References Dept (Deptno),
 Constraint Emp_Empno_Pk Primary Key (Empno)
)
Cluster EmpAndDept(Deptno);

Insert Into Emp Values(7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20);
Insert Into Emp Values(7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300,30);
Insert Into Emp Values(7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30);
Insert Into Emp Values(7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20);
Insert Into Emp Values(7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30);
Insert Into Emp Values(7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL,30);
Insert Into Emp Values(7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL,10);
Insert Into Emp Values(7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20);
Insert Into Emp Values(7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10);
Insert Into Emp Values(7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30);
Insert Into Emp Values(7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);
Insert Into Emp Values(7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30);
Insert Into Emp Values(7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL,20);
Insert Into Emp Values(7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);

------------------------------------------------------------------------
-- Get the data from the cluster
------------------------------------------------------------------------
Select *
  From Emp
  Join Dept
Using (DeptNo);

------------------------------------------------------------------------
-- Show the data dictionary information
------------------------------------------------------------------------
Column Single_Table Format A6

Select Cluster_Name, Tablespace_Name, Pct_Free, Pct_Used, Key_Size, Ini_Trans, Max_Trans, Initial_Extent, Next_Extent, Min_Extents, Max_Extents, Pct_Increase
  From User_Clusters;

Select Freelists, Freelist_Groups, Avg_Blocks_Per_Key, Cluster_Type, Function, Hashkeys, Degree, Instances, Cache, Buffer_Pool, Single_Table, Dependencies
  From User_Clusters;
  1  Select Column_name, Comments
  2    From All_Col_Comments
  3*  Where Table_Name = 'USER_CLUSTERS'
SYSTEM > 
SYSTEM > /

Column
Name                 Comments
-------------------- ------------------------------------------------------------------
CLUSTER_NAME         Name of the cluster
TABLESPACE_NAME      Name of the tablespace containing the cluster
PCT_FREE             Minimum percentage of free space in a block
PCT_USED             Minimum percentage of used space in a block
KEY_SIZE             Estimated size of cluster key plus associated rows
INI_TRANS            Initial number of transactions
MAX_TRANS            Maximum number of transactions
INITIAL_EXTENT       Size of the initial extent in bytes
NEXT_EXTENT          Size of secondary extents in bytes
MIN_EXTENTS          Minimum number of extents allowed in the segment
MAX_EXTENTS          Maximum number of extents allowed in the segment
PCT_INCREASE         Percentage increase in extent size
FREELISTS            Number of process freelists allocated in this segment
FREELIST_GROUPS      Number of freelist groups allocated in this segment
AVG_BLOCKS_PER_KEY   Average number of blocks containing rows with a given cluster key
CLUSTER_TYPE         Type of cluster: b-tree index or hash
FUNCTION             If a hash cluster, the hash function
HASHKEYS             If a hash cluster, the number of hash keys (hash buckets)
DEGREE               The number of threads per instance for scanning the cluster
INSTANCES            The number of instances across which the cluster is to be scanned
CACHE                Whether the cluster is to be cached in the buffer cache
BUFFER_POOL          The default buffer pool to be used for cluster blocks
SINGLE_TABLE         Whether the cluster can contain only a single table
DEPENDENCIES         Should we keep track of row level dependencies?
SYSTEM >