Re: Q: What is the easy way of 'sorting' a table physically?

Hi Mike,

that statement should have given you, depending on which version you are using, an ORA-00933 error - SQL command not properly ended, because of the order by.

Even if it had worked there would no guarantee that the rows would be stored in that order. Oracle distributes data according to its use of free space, so any query can never truly guarantee an order unless an order by is used in the retrieval of that data [ lets ignore group functions for a moment].

If you had an empty tablespace with a single table and inserted rows one at a time with an ascending sequence number id, it is probable that the data would come back in id order without an order by, but Oracle have always maintained that implicit ordering can not be relied upon.

You could of course do your inserts as separate inserts driven from within a cursor the data of which is ordered by an order by, [ set up a PLSQL procedure or block with acursor for loop ] but I would say that the above would still hold true.

If the reason for doing it is to improve performance because of fragmentation of distributed keys then I would think that the cause of the fragmentation should be addressed and consideration of clustering or other techniques to match the retrieval needs rather than trying to maintain the data in a specific order.

Given that the table would normally be indexed to correspond with the most critical retrieval requirements together with the probability that a query would encompass data
selected from across the table contents, then physically ordering of the data would commonly be self-defeating.

I hope I haven't drifted too far off the question,

cheers

Rod

Lane Associates
Lane_Associate_at_Compuserve.com
http://www.Lane-Associates.com

Mike C. wrote:
>
> Q: What is the easy way of 'sorting' a table physically?
>
> I've copy a table and tried this code below but unable to resort it...
> HELP!
>
> create table New_One as
> select *
> from Old_One
> order by First_Field
>
> Why is this not working??? Anyone??
Received on Wed Apr 15 1998 - 13:32:49 CDT