Oracle: Table Cluster with example

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);
----------------------------------------------------------

27 comments:

  1. wonderful explanation in brief for quick understanding -Dewang

    ReplyDelete
  2. thanks good info
    yatin j

    ReplyDelete
  3. how to see cluster name

    ReplyDelete
  4. very good information for beginers

    ReplyDelete
  5. please tell me why we use cluster..
    any example...?
    thank you

    ReplyDelete
  6. how can i use cluster to already created tables.??

    ReplyDelete
    Replies
    1. You can't add a new cluster to an existing table

      Delete
  7. I can use standard DML statements over cluster table?

    ReplyDelete
  8. Hi Nimish,
    I 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

    ReplyDelete
    Replies
    1. How can anyone decide your "business logic", this is defined as per the requirements and how it will be integrated with your current environment.

      Delete
  9. Very good basic explanation on clusters

    ReplyDelete
  10. Hi Nimish
    How can I find out the cluser key of a group of tables which are in the same cluster?

    ReplyDelete
  11. Hi Nimish,
    Please differentiate between subquery and correlated subquery.give an real time example.
    If it is possible to create pl/sql procedure in anonymous block.

    ReplyDelete
    Replies
    1. http://docs.oracle.com/cd/E17952_01/refman-5.1-en/correlated-subqueries.html

      Delete
  12. Hi Nimish,
    If 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...

    ReplyDelete
  13. Hi Nimish,
    I 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?

    ReplyDelete
    Replies
    1. trace and tkprof the query for both cluster and non cluster data. You will get the difference.

      Delete
  14. hi nimessh can plz give me an example how to show a created cluster?

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete