Home » SQL & PL/SQL » SQL & PL/SQL » ALTER TABLE STATEMENT FOR CLUSTER (Oracle 19C/Window 10)
ALTER TABLE STATEMENT FOR CLUSTER [message #678034] Fri, 01 November 2019 07:28 Go to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
Dear All.
I create cluster and then want to assign those cluster to created table but it display error.


SQL> create cluster clusempdept (department_id number);

Cluster created.

 1  alter table employees
  2*  CLUSTER clusempdept(department_id)
SQL> /
 CLUSTER clusempdept(department_id)
 *
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option


please help me in this regards, that how i can alter table and assign cluster to that table.
Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678035 is a reply to message #678034] Fri, 01 November 2019 07:30 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You can't convert a heap table into a clustered table. As you have discovered.
Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678036 is a reply to message #678035] Fri, 01 November 2019 07:36 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
thanks for message. please guide me that is it possible to convert existing table into clustered table?
Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678037 is a reply to message #678036] Fri, 01 November 2019 07:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Bilal Khan wrote on Fri, 01 November 2019 05:36
thanks for message. please guide me that is it possible to convert existing table into clustered table?

IMO, a one column table is pretty useless. What business problem does below solve?

create cluster clusempdept (department_id number);
Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678039 is a reply to message #678036] Fri, 01 November 2019 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bilal Khan wrote on Fri, 01 November 2019 13:36
thanks for message. please guide me that is it possible to convert existing table into clustered table?
As John said it is not possible.

Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678040 is a reply to message #678039] Fri, 01 November 2019 08:39 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I've been trying to think of a suitable metaphor. The best I have is cooking omelettes. You have two eggs, and beat up one of them to make one omelette, then you beat up the other to make a second omelette. It is impossible to combine them into one big omelette. To do that, you have to mix the eggs together before cooking them.

(ps - I can make a really good frittata).
Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678044 is a reply to message #678040] Fri, 01 November 2019 22:40 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
Thanks for every one, it mean that i have to recreate new table and convert into clustered table.
Re: ALTER TABLE STATEMENT FOR CLUSTER [message #678045 is a reply to message #678044] Sat, 02 November 2019 01:25 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No it means you have to create a cluster then a table into it there is no possible conversion.

But the question is: why do you want to create a "clustered table"?
Don't you confuse with SQL Server clustered table which is a complete different thing.

Previous Topic: Update null value columns based upon previous non null columns
Next Topic: AWR Report SQL with Bind Variables
Goto Forum:
  


Current Time: Thu Mar 28 13:04:29 CDT 2024