Home » SQL & PL/SQL » SQL & PL/SQL » "ALTER TABLE ... ORGANIZATION INDEX" error
"ALTER TABLE ... ORGANIZATION INDEX" error [message #409947] Wed, 24 June 2009 09:40 Go to next message
aboki
Messages: 3
Registered: June 2009
Junior Member
Hello World,

I am an oracle neobyte.

I had earlier created a table (trades) with a primary key (PK) on (date, stock).

Now i want to change the table structure so that all the rows are sorted by the PK using the following code:

ALTER TABLE TRADES ORGANIZATION INDEX


This caused the error:

ALTER TABLE TRADES ORGANIZATION INDEX
*

ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


Please help. Thanks in advance

[EDITED by LF: renamed the topic; the original title was "a"]

[Updated on: Wed, 24 June 2009 15:45] by Moderator

Report message to a moderator

Re: a [message #409949 is a reply to message #409947] Wed, 24 June 2009 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
When all else fails Read The Fine Manual
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
URL contains complete description of valid SQL syntax

In the future continue to provide a 100% total MEANINGLESS subject

[Updated on: Wed, 24 June 2009 09:46]

Report message to a moderator

Re: a [message #409950 is a reply to message #409947] Wed, 24 June 2009 09:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You cannot change a Heap organised table into an Index organised one.

Why are you doing this? Is it by any chance to try to get the data to come out in an ordered fashion?
Re: a [message #409954 is a reply to message #409950] Wed, 24 June 2009 10:12 Go to previous messageGo to next message
aboki
Messages: 3
Registered: June 2009
Junior Member
Thanks JRowbottom for the hint. i am really new to all these.

YES, without using ORDER BY.
Re: a [message #409957 is a reply to message #409949] Wed, 24 June 2009 10:19 Go to previous messageGo to next message
aboki
Messages: 3
Registered: June 2009
Junior Member
Thanks BlackSwan,

now, i know i can always count on you replying to each and every post of mine inspite of your very busy, useful and fruitful life.
Re: a [message #409960 is a reply to message #409954] Wed, 24 June 2009 10:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid you're wasting your time, in more ways than one - if you want to guarantee that the results come out in a specific order, then you need to use order by.

From the documentation
Quote:
order_by_clause

Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
Re: a [message #409961 is a reply to message #409954] Wed, 24 June 2009 10:27 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
aboki wrote on Wed, 24 June 2009 16:12
Thanks JRowbottom for the hint. i am really new to all these.

YES, without using ORDER BY.


It is possible I believe to query an index organised table and get the data back in a different order.

If you want the data ordered use an order by in all circumstances.
Previous Topic: Build logic
Next Topic: GTT Problem
Goto Forum:
  


Current Time: Thu Dec 08 20:37:49 CST 2016

Total time taken to generate the page: 0.24894 seconds