4

Is it possible to add cluster to an existing table? For example...
I have a table:

CREATE TABLE table_name(  
t_id number PRIMARY KEY,  
t_name varchar2(50));

Cluster:

CREATE CLUSTER my_cluster
(c_id NUMBER) SIZE 100;

Is there a command like: ALTER TABLE t_name ADD CLUSTER my_cluster(t_id); or something like that?
Because I want table to look something like this:

CREATE TABLE table_name(  
t_id number PRIMARY KEY,  
t_name varchar2(50))  
CLUSTER my_cluster(t_id);

And dropping all connected tables isn't really what I want to do.
Thanks

Amro
  • 123,847
  • 25
  • 243
  • 454
randomUser56789
  • 936
  • 2
  • 13
  • 32
  • Out of interest, why do you want to build a cluster. I've been working with Oracle for almost twenty years and I've never come across a case for them. – APC Oct 09 '11 at 07:43
  • Thing is I don't want to build a cluster, I just have to for my university task. – randomUser56789 Oct 09 '11 at 10:52
  • it is sad that universities are wasting their students' time with things that they will *never* use in real life. Good luck with your studies :) – APC Oct 10 '11 at 10:32

1 Answers1

-1

You really need to understand what a cluster really is. From the docs:

"Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves." (emphasis mine)

The point being, the tables in a cluster are co-located. This is a physical arrangement. So, for the database to cluster existing tables we must drop and re-create them.

It is possible to minimise the downtime by building the clustered table under a different name. You will need to keep the data in synch with the live table until you are ready to swap. You will need to restrict access to the database while you do this, to prevent data loss. Then you rename the old table, rename the clustered table with the proper name, run the necessary grants and recompile invalid procedures, synonyms, etc.

APC
  • 144,005
  • 19
  • 170
  • 281
  • I just asked. I don't know how I wanted to achieve it, I am a newb to databases. Ok then I guess I will drop and recreate or recreate copies with different name. – randomUser56789 Oct 09 '11 at 10:55
  • need appropriate answer – Md Nazrul Islam Mar 05 '19 at 08:29
  • It is impossible for me to recreate a table for cluster. I need a solution for existing Table (any how). – Md Nazrul Islam Mar 05 '19 at 08:56
  • Why do you need to cluster your table? If the anticipated benefits are so great that they justify an outage then you should go ahead. Otherwise you should consider whether clustering is the solution you need. – APC Mar 05 '19 at 09:26
  • @nislam - I have expanded my answer with an approach which minimises downtime. But there is no solution which offers zero outage. – APC Mar 05 '19 at 09:34