top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is the use of CLUSTER?

+1 vote
What is the use of CLUSTER?
posted Jul 22, 2015 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes

Simply put, a cluster ties data values to disk location.
A cluster key is used to group data together. All rows of all tables with the same value of the cluster key are stored in the same data block.

The killer feature of table clusters is that you can store related rows of different tables at the same physical location.

That can improve join performance by an order of magnitude. However, it doesn't pay of so often as it sounds.

The only time I used it was a three-table join, executed by two hash joins. It took too long ;). However, the join was on the same column, so it was possible to use a hash table cluster keyed by the join column. That caused all related rows to be stored alongside (ideally, in the same database block). Knowing that, Oracle can execute the join with a special optimization ("cluster join").

It's more or less pre-joined, but still feeling like normal tables (for INSERT/SELECT/UPDATE/DELETE).

On the other hand, there are "single table clusters" that are mostly used to control the "clustering factor" -- A similar idea like clustered indexes (called Index-Organized-Table in Oracle) but not adding high cost if using a secondary index.

answer Jul 22, 2015 by Amit Kumar Pandey
0 votes

Clusters are an optional method of storing table data. A Cluster is a group of tables that share the same data blocks because they share common columns and are often used together. It improves access time for joins of clustered tables.

answer Jul 23, 2015 by Arun Gowda