Home » SQL & PL/SQL » SQL & PL/SQL » Re-Ordering a Table
Re-Ordering a Table [message #185417] Tue, 01 August 2006 11:45 Go to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
OK....I know you guys think I'm nuts on this one. I think I've found a way to re-order the rows in a table, and I want to run it by those who can tell me if I'm missing something. The table that I'm dealing with (CLIENT_TBL) will remain unchanged after our clients go live.....so this will only need to be done once.

CREATE TABLE TEMP AS SELECT * FROM CLIENT_TBL P
ORDER BY P.FIELD1;
DROP TABLE CLIENT_TBL;
ALTER TABLE TEMP RENAME TO CLIENT_TBL;
Re: Re-Ordering a Table [message #185419 is a reply to message #185417] Tue, 01 August 2006 11:48 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Why? What is this accomplishing?
Re: Re-Ordering a Table [message #185421 is a reply to message #185419] Tue, 01 August 2006 12:03 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
When our application starts up, clients are given a choice of "PayGroups" to select. Generally, clients prefer that they appear in alpha order. But, the selector pull the PayGroups directly from the table, without any sorting. This can be an issue, because the table is altered frequently during implementation, and very infrequently altered after they go live.

Due to the standardization of the application:
I can't add sorting to the view that the selector uses.
I can't tell the selector to use a different view with a sort.
But, I can play around with the database.

Development is going to address the issue....next year if we're lucky. So, I'm looking for an interim solution.

Re: Re-Ordering a Table [message #185425 is a reply to message #185421] Tue, 01 August 2006 12:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Are you going to do this daily? A single delete from this table and then a single insert will most likely cause another unsorted list.
But also possible is that without an ORDER BY in this application even without any changes to the table, it can still give you an unsorted list.
I don't know, to me changing the view (or adding a vies) to add a simple ORDER BY clause seems like almost no work. It takes a year in your location to do this? Talk about red tape to get a simple task done.
What application is this that is so inflexible?
Re: Re-Ordering a Table [message #185429 is a reply to message #185425] Tue, 01 August 2006 12:44 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
This wouldn't be done daily....it would be done right before a client goes live. And the table would likely remian unchanged for a long time.....

I agree that simply changing the query used by the selector would be the easiest way, but development is rather methodical about these things, and will add it to the next release package.

I just wanted to make sure that my script wasn't going to cause some issues with the database....it seems to work just as I'd like it to.
Re: Re-Ordering a Table [message #185446 is a reply to message #185429] Tue, 01 August 2006 16:15 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
OK, but just remember that the order of a result set is never guaranteed without an ORDER BY clause. It may return the rows in so-called "natural" order today, but it might not tomorrow...
Re: Re-Ordering a Table [message #185623 is a reply to message #185417] Wed, 02 August 2006 14:17 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
The way to handle this is NOT to sort the data, oracle will NEVER guarantee the order returned from a select. The correct way is to contact the developers of the application and inform them that they have an error in the application and have them add an order clause on the select.
Re: Re-Ordering a Table [message #185644 is a reply to message #185623] Wed, 02 August 2006 17:36 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
As I pointed out below....

"Development is going to address the issue....next year if we're lucky. So, I'm looking for an interim solution."

I know it may be not perfect, but if software wasn't released unless it were perfect....there wouldn't be a lot of software on the market.

Re: Re-Ordering a Table [message #185671 is a reply to message #185644] Thu, 03 August 2006 00:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

if software wasn't released unless it were perfect....there wouldn't be a lot of software on the market.


True, but if software wasn't released as soon as possible and with so many 'known issues' as now often is the case, the industry probably would not have such a bad image.
Previous Topic: Regarding tables index by binary_integer
Next Topic: I know column name but dont know from which table
Goto Forum:
  


Current Time: Fri Dec 02 20:40:37 CST 2016

Total time taken to generate the page: 0.09757 seconds