Clustering

Clustering :

It is a mechanism to bind the data together.. to achieve clustering clusters are used...

syntax for creating a cluster :

create cluster <cluster_name>
( cluster_column <data_type>  )

ex :
create cluster deptno_cluster
( deptno number );

Syntax for Creating a cluster Table :

create table <table_name>
(
column_name <data_type>,
.
.
cluster_column <data_type>
)cluster cluster_name( cluster_column )

create table emp_cluster_tab
(
empno number,
ename varchar2(10),
sal number,
deptno number
)cluster deptno_cluster( deptno )

Note : Cluster tables cant be used before indexing the cluster...

Indexes :

Usage : It is used for improving the performance of retrieval of data whenever the search condition is specified is an indexed column..

syntax :

create index <index_name>
on table table_name( column_name )

create index ename_ind on table emp( ename )

select * from emp where ename = 'SMITH'

Rules for using Indexes :

* Table should contain more than 20000 records

* Table row's should be large with more no. of null values in a column..

* If the table's data is more and only 2-4% of its data should be retrieved

* If a column is very frequently used in a where or join condition..

Note :
* The info. about the indexes can be retrieved from user_indexes..

select * from user_indexes where index_name like 'ENAME_IND'

* To retrieve the info. about the columns on which indexes are defined can be retrieved from user_ind_columns..

select column_name from user_ind_columns
where table_name = 'EMP';

unique index : If a column is defined with an unique index then it will implictly define an unique constraint on that column..

syntax :
create unique index <index_name>
on table table_name( column )

create unique index eno_ind on emp( empno )

Cluster Index : To index a cluster it is used..
syntax :
create index <index_name>
on cluster cluster_name;

create index deptno_cluster_index
on cluster deptno_cluster;


insert into emp_cluster_tab
values( 101,'sekhar',15000,10 );

select * from emp_cluster_tab;

Note :
* The information about the clusters can be retrieved from user_clusters..