On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Sunday, August 31, 2008

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 >

No comments:

Post a Comment