Clustering is a method of storing tables that are intimately related and often joined together into the same area on disk. The cluster key is the column or columns by which the tables are usually joined in a query
By storing the field comprising the Cluster Key once instead of multiple times, storage is saved. The arguably more significant advantage to Clustering is to expidite join queries. When a query is fired that joins these 2 tables by Cluster Key, the joined rows would be fetched with a single IO operation.
A cluster is a data structure that improves retrieval performance
Example:
----------------------------------------------------------
create cluster empdept (did number(2));
----------------------------------------------------------
create index empdept_indx on cluster empdept;
----------------------------------------------------------
create table emp
(
eid number(10),
ename varchar2(100),
did number(2)
)
cluster empdept(did);
----------------------------------------------------------
create table dept
(
did number(2),
dname varchar2(100)
)
cluster empdept(did);
----------------------------------------------------------
By storing the field comprising the Cluster Key once instead of multiple times, storage is saved. The arguably more significant advantage to Clustering is to expidite join queries. When a query is fired that joins these 2 tables by Cluster Key, the joined rows would be fetched with a single IO operation.
A cluster is a data structure that improves retrieval performance
Example:
----------------------------------------------------------
create cluster empdept (did number(2));
----------------------------------------------------------
create index empdept_indx on cluster empdept;
----------------------------------------------------------
create table emp
(
eid number(10),
ename varchar2(100),
did number(2)
)
cluster empdept(did);
----------------------------------------------------------
create table dept
(
did number(2),
dname varchar2(100)
)
cluster empdept(did);
----------------------------------------------------------
wonderful explanation in brief for quick understanding -Dewang
ReplyDeletethanks good info
ReplyDeleteyatin j
how to see cluster name
ReplyDeleteuser_clusters
Deletecan see by user_sorce
Deletevery good information for beginers
ReplyDeleteThank you for good example
ReplyDeleteplease tell me why we use cluster..
ReplyDeleteany example...?
thank you
Performance of Equi Join improves..
Deletehow can i use cluster to already created tables.??
ReplyDeleteYou can't add a new cluster to an existing table
DeleteI can use standard DML statements over cluster table?
ReplyDeleteyes u can
DeleteHi Nimish,
ReplyDeleteI need to be create pl/sql stored procedure for bank check collection.Please send me the business logic.
it was better to create that.
Thanks in advance...
This is my mail manojramchandren@gmail.com
How can anyone decide your "business logic", this is defined as per the requirements and how it will be integrated with your current environment.
DeleteVery good basic explanation on clusters
ReplyDeleteHi Nimish
ReplyDeleteHow can I find out the cluser key of a group of tables which are in the same cluster?
Hi Nimish,
ReplyDeletePlease differentiate between subquery and correlated subquery.give an real time example.
If it is possible to create pl/sql procedure in anonymous block.
http://docs.oracle.com/cd/E17952_01/refman-5.1-en/correlated-subqueries.html
DeleteThanks a lot nimish,
ReplyDeleteHi Nimish,
ReplyDeleteIf user create a object with out table space where oracle stored the segment?
and
In which time we created server side procedure?
Thanks in advance...
Default Tablespace
DeleteHi Nimish,
ReplyDeleteI used the same table and ran the xplain plan.
insert into emp
select level,to_char(ascii(level)),level*100
from dual connect by level<=2000;
insert into dept
select level*100,to_char(ascii(level))
from dual connect by level<=1000;
Plan hash value: 3940750165
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 14000 | 960 (1)| 00:00:12 |
| 1 | MERGE JOIN | | 1000 | 14000 | 960 (1)| 00:00:12 |
| 2 | TABLE ACCESS CLUSTER| EMP | 2000 | 14000 | 413 (0)| 00:00:05 |
| 3 | INDEX FULL SCAN | EMPDEPT_INDX | 2000 | | 13 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 1000 | 7000 | 547 (1)| 00:00:07 |
| 5 | TABLE ACCESS FULL | DEPT | 1000 | 7000 | 546 (1)| 00:00:07 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."DID"="B"."DID")
filter("A"."DID"="B"."DID")
Can you explain how cluster above is being used ?
And how it decreases the I/O?
trace and tkprof the query for both cluster and non cluster data. You will get the difference.
Deletesuperb example
ReplyDeletehi nimessh can plz give me an example how to show a created cluster?
ReplyDeleteThis comment has been removed by the author.
ReplyDelete