| Re-Order a table [message #184230] |
Tue, 25 July 2006 16:05  |
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 #184253 is a reply to message #184232] |
Tue, 25 July 2006 22:29  |
rleishman
Messages: 3728
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 |
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 |
rleishman
Messages: 3728
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 #184312 is a reply to message #184255] |
Wed, 26 July 2006 02:23 |
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 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
