Home » SQL & PL/SQL » SQL & PL/SQL » Re-Order a table
Re-Order a table [message #184230] Tue, 25 July 2006 16:05 Go to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
I'm working with an application that pulls data from a table, and the view that is used pulls the data in the order that exists naturally in the table. Unfortunately, that order is not desirable.

Is there a way to re-order a table?
Re: Re-Order a table [message #184232 is a reply to message #184230] Tue, 25 July 2006 16:13 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Can you just recreate the view? If not, you may want to consider the DBMS_REDEFINITION package to reconfigure your table.
Re: Re-Order a table [message #184253 is a reply to message #184232] Tue, 25 July 2006 22:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can control the "natural" order of rows in a table (with difficulty) but there is NO WAY to ensure rows are selected in that order without an ORDER BY.

Just in case that's not clear, let me say it this way:

If you need a query to return rows in a preferred order, you can use any of the methods listed below:
1. Use and ORDER BY clause.


Just to be complete (and hopefully cut your fruitless search a bit shorter):

- It is possible to order the rows in a table by rebuilding it (CREATE TABLE new AS SELECT * FROM old ORDER BY a,b,c...). The table WILL NOT stay ordered. New inserts will just slot in to any old free space, and updates could potentially upset the order as well. What's more, Oracle makes no guarantees that a SELECT will return rows in that order anyway. Parallel Query server is a classic - rows come back in strange order.

- It is possible to create an ordered hash cluster which will keep the rows in a specified order. The overheads are huge though, and there's still no guarantee that rows will return in "natural" order.

- There are other "tricks" to getting rows returning in a preferred order (index range scan; group by) but none of these will GUARANTEE an order. The only way to do that is with an ORDER BY.

Just use an ORDER BY.

Ross Leishman
Re: Re-Order a table [message #184255 is a reply to message #184253] Tue, 25 July 2006 22:40 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
I am stuck with a view that does not order the rows in any way...so I was looking for a way to re-order the table directly. Are you saying this might work:

CREATE TABLE PS_PAYGROUP_TBL AS SELECT * FROM PS_PAYGROUP_TBL ORDER BY PAYGROUP;
Re: Re-Order a table [message #184258 is a reply to message #184255] Tue, 25 July 2006 22:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No. You cannot re-order a table in-place. It will tell you that it already exists. You have to rebuild to a different name and then rename it.
And NO, this is not a viable solution for the reasons above.

Ross Leishman
Re: Re-Order a table [message #184282 is a reply to message #184253] Wed, 26 July 2006 00:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
rleishman wrote on Wed, 26 July 2006 05:29

Just use an ORDER BY.
I'm with Ross here.

MHE
Re: Re-Order a table [message #184312 is a reply to message #184255] Wed, 26 July 2006 02:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the view that you are using doesn't order the data then you could:

1) Create a new view on top of your existing one, and have the 2nd view use an ORDER BY

2) Just use an ORDER BY in the SELECT statements that get data from the view.
Re: Re-Order a table [message #184505 is a reply to message #184312] Wed, 26 July 2006 17:50 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
I appreciate all suggestions...unfortunately, I don't have control over the view is being used. I can't add an ORDER BY clause, nor can I use a different view.

That part of the application isn't customizable..

Thanks everyone!
Re: Re-Order a table [message #184576 is a reply to message #184505] Thu, 27 July 2006 02:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You must presumably have control over your query from the view? Or is this one of those nasty situations where you get asked to change a result without being allowed to change anything that is involved in producing the result.

If so, you have my sympathy.
Previous Topic: Display images stored in blob
Next Topic: How to write a data file (simple flat file) using PL/SQL List
Goto Forum:
  


Current Time: Sat Dec 03 18:02:30 CST 2016

Total time taken to generate the page: 0.24153 seconds