ALTER TABLE question

[mark.g...@lycos.co.uk]

Hello, I need to alter a table by adding a column and adding this
column to a cluster already on the table. I don't know how to do it.
There is a section in the Oracle online documentation on altering
clustered tables but it doesn't really help.
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/clustrs004.htm#sthref2105

I cannot find an example on Morgans library either.

If I was to create my existing table I would use something like the
following. I have simplified it considerably just to get my point
across. The production implementation is far from this simple.

create cluster testcluster(no1 number,no2 number) size 300 index;
create table testtable(no1 number,no2 number) cluster
testcluster(no1,no2);

If I was to create my new structure table and cluster, I would use the
following.

create cluster testcluster(no1 number,no2 number,no3 number) size 300
index;
create table testtable(no1 number,no2 number,no3 number) cluster
testcluster(no1,no2,no3);

What I want to do is to MODIFY my existing table and cluster however
so that they have the new structure.

Can this be done and if so, how.

This is my first post to this newsgroup and my apologies if I am being
too direct in my request for help. Finally we are currently 11.1.0 on
Solaris 10. Thank you for reading.

Mark

[DA Morgan]

I know of no way to do it.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[joel garry]

On May 26, 11:37 am, mark.good...@lycos.co.uk wrote:
> Hello, I need to alter a table by adding a column and adding this
> column to a cluster already on the table. I don't know how to do it.
> There is a section in the Oracle online documentation on altering

> clustered tables but it doesn't really help.http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/clustr...

>
> I cannot find an example on Morgans library either.
>
> If I was to create my existing table I would use something like the
> following. I have simplified it considerably just to get my point
> across. The production implementation is far from this simple.
>
> create cluster testcluster(no1 number,no2 number) size 300 index;
> create table testtable(no1 number,no2 number) cluster
> testcluster(no1,no2);
>
> If I was to create my new structure table and cluster, I would use the
> following.
>
> create cluster testcluster(no1 number,no2 number,no3 number) size 300
> index;
> create table testtable(no1 number,no2 number,no3 number) cluster
> testcluster(no1,no2,no3);
>
> What I want to do is to MODIFY my existing table and cluster however
> so that they have the new structure.
>
> Can this be done and if so, how.

Since the idea of clusters is to have different tables rows physically
near each other for quick access, what you are asking is how to change
the cluster key. If there were syntax to do that, Oracle would have
to rewrite every row. So you might as well be forced to create it all
new.

>
> This is my first post to this newsgroup and my apologies if I am being
> too direct in my request for help. Finally we are currently 11.1.0 on
> Solaris 10. Thank you for reading.
>

We wish everyone would be so direct! The only nitpick is to post all
four parts of the Oracle version.

jg
--
@home.com is bogus.
So why can't they target trolls? http://www.signonsandiego.com/uniontrib/20080527/news_1b27target.html
word: hotenti (hot, ain't I?)