databaseoracle-databasealter-tabledatabase-cluster

Add cluster to an existing table in oracle

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

Solution

  • 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.