Q
Problem solve Get help with specific problems with your technologies, process and projects.

Clusters explained

I want to know what is a cluster in Oracle, and how can I create it?

In Oracle, a cluster is a way to pre-join two tables and physically store them on disk pre-joined. For instance, suppose I have these two tables:

Table_A:
ID   Name
---  ------
  1  Bob
  2  Sally
  3  June
  4  Jane

Table_B:
ID   Address
---  -----------
  1  123 Main St.
  3  1212 Oak Ave.
  4  888 1st St.
  2  101 Municipal Way
If I'm always joining these two tables on the ID column, I can cluster them together so that they are physically stored on disk as follows.
Cluster_A_B:
Name   ID   Address
------ ---  -------
Bob      1  123 Main St.
Sally    2  101 Municipal Way
June     3  1212 Oak Ave.
Jane     4  888 1st St.
To create this cluster, I must first define it before I create the tables. For my example, I would issue the following command:
CREATE CLUSTER Cluster_A_B (ID NUMBER);
Then I would create the two tables and tie them to the cluster as follows:
CREATE TABLE Table_A (
   ID NUMBER,
   Name VARCHAR2(20))
CLUSTER Cluster_A_B;

CREATE TABLE Table_B (
   ID NUMBER,
   Address VARCHAR2(20))
CLUSTER Cluster_A_B;
Now that you are physically storing tables "together" in a cluster, you will experience performance problems accessing these tables seperately. For this very reason, very few people actually use clusters in an Oracle database.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close