Hysterical ordering of data
May 11, 2007 9:12 PM Subscribe
I am currently dealing with an Oracle-based application that on many of the screens returns data in what I call "hysterical order" (seemingly random to the casual observer).
In fact, the application applies no ORDER BY logic to the underlying data, which ORACLE returns in an undefined order (actually the order the data was entered). This is useless in practice.
I don't have the ability to change the application in any way, but I do have access to the database.
I am aware that it is non-standard in relational database theory to be able to specify a default ordering for tables, but in Oracle, are there in practice, any modifications I could make to the underlying tables that will not cause the app to break, but will allow me to specify the default order of the data when there is no ORDER BY used? I can identify a column in each table which should be the default order.
It is a long time since I was a DBA, so I am a little rusty on current Oracle practice and capabilities.
Oracle does not have the ability to set a default sort order for tables. In fact, it won't even always return data in the order it was entered, but rather whatever order the optimizer decides is the fastest way to return the results of the query.
If you're unable to change the code that's querying the tables, to add an ORDER BY clause to each, I think there really isn't any way to do this.
posted by cerebus19 at 9:50 PM on May 11, 2007
If you're unable to change the code that's querying the tables, to add an ORDER BY clause to each, I think there really isn't any way to do this.
posted by cerebus19 at 9:50 PM on May 11, 2007
No.
If performance is free, how about a trigger on UPDATE or INSERT, which resorts the table per your criteria?
This will make your servers cry.
posted by chundo at 9:50 PM on May 11, 2007
If performance is free, how about a trigger on UPDATE or INSERT, which resorts the table per your criteria?
This will make your servers cry.
posted by chundo at 9:50 PM on May 11, 2007
I wonder if you could see what indexes are being hit by the query and see if you can't tweak the indexing a little to get it to hit one that is in the order you'd like to see the data in--that may or may not result in a change in the way the results come back.
posted by jacquilynne at 10:02 PM on May 11, 2007
posted by jacquilynne at 10:02 PM on May 11, 2007
You might be able to something by dumping the table rows to a file in the desired order, dropping and recreating the table, and re-adding the rows in the desired order. It's been ages since I used Oracle, but this might work, might not work, or it might work most of the time with occasional random hiccups.
You do seem to be aware that this makes relational baby Jesus cry, and that the right solution is to add an ORDER BY to the qery — but if you can't do that, this might be worth a try.
Of course you'll have to do it repeatedly, if new rows are being added to that table…
I suggest re-examining this broken application and making really sure you don't have some way to slip an ORDER BY in there. Hex editor on the binary? Shared library shim?
posted by hattifattener at 10:35 PM on May 11, 2007
You do seem to be aware that this makes relational baby Jesus cry, and that the right solution is to add an ORDER BY to the qery — but if you can't do that, this might be worth a try.
Of course you'll have to do it repeatedly, if new rows are being added to that table…
I suggest re-examining this broken application and making really sure you don't have some way to slip an ORDER BY in there. Hex editor on the binary? Shared library shim?
posted by hattifattener at 10:35 PM on May 11, 2007
trigger: sorting in the database
ORDER: sorting in the query
order: sorting in the application code
posted by rhizome at 11:12 PM on May 11, 2007
ORDER: sorting in the query
order: sorting in the application code
posted by rhizome at 11:12 PM on May 11, 2007
Hmm... totally pulling stuff out of my ass, but here we go:
1) rename the table to move it out of the way.
2) create a simple view of the renamed table but add your ORDER BY to the subquery.
3) this new view is not inheriantly updatable. So, if you need insert/update/delete then you'll have to create INSTEAD OF triggers.
Here's the Oracle document on views for 10g release 2. Specifically, you'll want to take a look at the INSTEAD OF trigger example.
This isn't for the faint of heart. Make sure you know what you're doing. And I won't be held responsible if you fuck things up royally :).
posted by sbutler at 11:14 PM on May 11, 2007
1) rename the table to move it out of the way.
2) create a simple view of the renamed table but add your ORDER BY to the subquery.
3) this new view is not inheriantly updatable. So, if you need insert/update/delete then you'll have to create INSTEAD OF triggers.
Here's the Oracle document on views for 10g release 2. Specifically, you'll want to take a look at the INSTEAD OF trigger example.
This isn't for the faint of heart. Make sure you know what you're doing. And I won't be held responsible if you fuck things up royally :).
posted by sbutler at 11:14 PM on May 11, 2007
I have to try this out myself but I believe you can:
1) Recreate the table using an ORDER BY clause
(Rename it to X then do a CREATE TABLE AS SELECT * FROM X ORDER BY...)
2) Create an INDEX on that key.
The Cost-based Optimizer will use that Index when returning rows and thus return them in the Key order.
Again, I haven't tried this but it seems that it should work.
posted by vacapinta at 1:08 AM on May 12, 2007
1) Recreate the table using an ORDER BY clause
(Rename it to X then do a CREATE TABLE AS SELECT * FROM X ORDER BY...)
2) Create an INDEX on that key.
The Cost-based Optimizer will use that Index when returning rows and thus return them in the Key order.
Again, I haven't tried this but it seems that it should work.
posted by vacapinta at 1:08 AM on May 12, 2007
Hmm. Move the table to oldtable. Create a view called table that has your ORDER BY on it (total guesswork - I do this to fake old, un-normalised DB structures for old applications). I assume you've checked that the application really is using embedded SQL, and isn't calling stored procedures in the DB?
posted by Leon at 2:06 AM on May 12, 2007
posted by Leon at 2:06 AM on May 12, 2007
This is a great question for Ask Tom (when he doesn't have a backlog). He'll give you the best solution that doesn't destroy performance or just plain break your application. Be sure to tell him your current Oracle version.
posted by loosemouth at 4:21 AM on May 12, 2007
posted by loosemouth at 4:21 AM on May 12, 2007
+1 on the rename table and create view with the original table n ame
posted by cmicali at 5:22 AM on May 12, 2007
posted by cmicali at 5:22 AM on May 12, 2007
Response by poster: Ideas so far are welcome.
vacapinta: close to what I'm thinking, I think.
Basically, is there a way to fool the optimizer without disturbing the table per se.
posted by blue_wardrobe at 6:37 AM on May 12, 2007
vacapinta: close to what I'm thinking, I think.
Basically, is there a way to fool the optimizer without disturbing the table per se.
posted by blue_wardrobe at 6:37 AM on May 12, 2007
Response by poster: Also, some stored procedures are being used. What difference does this make?
posted by blue_wardrobe at 6:40 AM on May 12, 2007
posted by blue_wardrobe at 6:40 AM on May 12, 2007
Response by poster: For the future, btw, I would suggest to the designers that all read-only access be done through a view over each table, to ensure that the order and selection criteria can be adjusted arbitrarily in future for practicality / security processes.
Good idea? Bad idea?
posted by blue_wardrobe at 6:43 AM on May 12, 2007
Good idea? Bad idea?
posted by blue_wardrobe at 6:43 AM on May 12, 2007
Response by poster: Which leads to the question of whether Virtual Database constructs can apply a sort order?
posted by blue_wardrobe at 6:44 AM on May 12, 2007
posted by blue_wardrobe at 6:44 AM on May 12, 2007
Best answer: Yo there are a few good answers and many really horrible ones in this thread. Disappointing actually.
In Oracle, there is no way to reliably retrieve data correctly sorted without using ORDER BY. Period. Please stop trying. No exceptions. No, group by wont do it either.
Either put the order by in your app or use a view as described above.
Both Tom Kyte and Jonathon Lewis have written extensively on several occasions about this.
-LC
posted by Slenny at 7:10 AM on May 12, 2007
In Oracle, there is no way to reliably retrieve data correctly sorted without using ORDER BY. Period. Please stop trying. No exceptions. No, group by wont do it either.
Either put the order by in your app or use a view as described above.
Both Tom Kyte and Jonathon Lewis have written extensively on several occasions about this.
-LC
posted by Slenny at 7:10 AM on May 12, 2007
Views should never have an ORDER BY clause. Views are supposed to appear just like a table, and tables aren't ordered, so views shouldn't be, either. I don't even know that Oracle supports it—it definitely didn't use to a few years ago, when I recall looking it up just for kicks.
Keep in mind that ordering in queries by necessity reduces performance. To order a query, the database must first retrieve all rows matching the WHERE clause, then order them, and only then return them. If a view were ordered, and you queried said view with another ORDER BY clause, this ordering would happen twice, once when the view's results are generated, and once when the end query's results are generated.
posted by cerebus19 at 7:17 AM on May 12, 2007
Keep in mind that ordering in queries by necessity reduces performance. To order a query, the database must first retrieve all rows matching the WHERE clause, then order them, and only then return them. If a view were ordered, and you queried said view with another ORDER BY clause, this ordering would happen twice, once when the view's results are generated, and once when the end query's results are generated.
posted by cerebus19 at 7:17 AM on May 12, 2007
Best answer: For the future, btw, I would suggest to the designers that all read-only access be done through a view over each table, to ensure that the order and selection criteria can be adjusted arbitrarily in future for practicality / security processes.
Good idea? Bad idea?
_________________
Really bad idea... except for security perhaps. What a messy idea.
What is wrong with order by? For new code, put this in your app.
The SELECT grant is used for read-only access. And many views are updateable anyway. A general rule of thumb is that if a base table is key-preserved, DML can be performed against the view.
@cerebus19
Your post is looney. Views are not supposed to appear just like a table. That is the whole point if a view.
And Oracle has supported order by in views for a very very long time.... since before version 8 for sure.
So much nonsense in this thread.
I would highly encourage you to post your oracle questions on the OTN forums.
http://forums.oracle.com/forums/forum.jspa?forumID=61&start=0
posted by Slenny at 7:35 AM on May 12, 2007
Good idea? Bad idea?
_________________
Really bad idea... except for security perhaps. What a messy idea.
What is wrong with order by? For new code, put this in your app.
The SELECT grant is used for read-only access. And many views are updateable anyway. A general rule of thumb is that if a base table is key-preserved, DML can be performed against the view.
@cerebus19
Your post is looney. Views are not supposed to appear just like a table. That is the whole point if a view.
And Oracle has supported order by in views for a very very long time.... since before version 8 for sure.
So much nonsense in this thread.
I would highly encourage you to post your oracle questions on the OTN forums.
http://forums.oracle.com/forums/forum.jspa?forumID=61&start=0
posted by Slenny at 7:35 AM on May 12, 2007
Your post is looney. Views are not supposed to appear just like a table. That is the whole point if a view.
Uh, no, but thank you for playing. The whole point of a view is to make the results of a query appear just like a table. From the point of view of an application or user querying the view, it should act exactly like a table. Views are even sometimes updatable, just like...tables!
posted by cerebus19 at 8:04 AM on May 12, 2007
Uh, no, but thank you for playing. The whole point of a view is to make the results of a query appear just like a table. From the point of view of an application or user querying the view, it should act exactly like a table. Views are even sometimes updatable, just like...tables!
posted by cerebus19 at 8:04 AM on May 12, 2007
Oracle has supported order by in views for a very very long time.... since before version 8 for sure.
Yes, you're right. I should have been clearer. Oracle doesn't support ORDER BY in updatable views.
posted by cerebus19 at 8:07 AM on May 12, 2007
Yes, you're right. I should have been clearer. Oracle doesn't support ORDER BY in updatable views.
posted by cerebus19 at 8:07 AM on May 12, 2007
@cerebus19
Yep you are right. Sorry I read an extra not in your post that was not there.
I agree with everything you posted except views not supporting the order by clause.
Cheers!
posted by Slenny at 8:10 AM on May 12, 2007
Yep you are right. Sorry I read an extra not in your post that was not there.
I agree with everything you posted except views not supporting the order by clause.
Cheers!
posted by Slenny at 8:10 AM on May 12, 2007
In fact oracle does support ORDER BY in updateable views.
These are the general constucts that force us to lose key preservation:
* DISTINCT operator
* Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
* Set operations: UNION, UNION ALL, INTERSECT, MINUS
* GROUP BY or HAVING clauses
* START WITH or CONNECT BY clauses
* ROWNUM pseudocolumn
system@GRAY> -- updating a view with order by....
system@GRAY> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
system@GRAY> drop table t purge;
Table dropped.
system@GRAY> create table t as select * from dba_tables where rownum < 200;br>
Table created.
system@GRAY> CREATE UNIQUE INDEX PK_t ON t (table_name, owner);
Index created.
system@GRAY> ALTER TABLE t ADD ( CONSTRAINT PK_T PRIMARY KEY (table_name, owner));
Table altered.
system@GRAY> create or replace view v as select * from t order by owner;
View created.
system@GRAY> update v set last_analyzed = sysdate;
199 rows updated.
system@GRAY> commit;
Commit complete.
system@GRAY> create or replace view v as select distinct * from t order by owner;
View created.
system@GRAY> update v set last_analyzed = sysdate;
update v set last_analyzed = sysdate
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
system@GRAY> commit;
Commit complete.
system@GRAY>
posted by Slenny at 8:35 AM on May 12, 2007
These are the general constucts that force us to lose key preservation:
* DISTINCT operator
* Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
* Set operations: UNION, UNION ALL, INTERSECT, MINUS
* GROUP BY or HAVING clauses
* START WITH or CONNECT BY clauses
* ROWNUM pseudocolumn
system@GRAY> -- updating a view with order by....
system@GRAY> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
system@GRAY> drop table t purge;
Table dropped.
system@GRAY> create table t as select * from dba_tables where rownum < 200;br>
Table created.
system@GRAY> CREATE UNIQUE INDEX PK_t ON t (table_name, owner);
Index created.
system@GRAY> ALTER TABLE t ADD ( CONSTRAINT PK_T PRIMARY KEY (table_name, owner));
Table altered.
system@GRAY> create or replace view v as select * from t order by owner;
View created.
system@GRAY> update v set last_analyzed = sysdate;
199 rows updated.
system@GRAY> commit;
Commit complete.
system@GRAY> create or replace view v as select distinct * from t order by owner;
View created.
system@GRAY> update v set last_analyzed = sysdate;
update v set last_analyzed = sysdate
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
system@GRAY> commit;
Commit complete.
system@GRAY>
posted by Slenny at 8:35 AM on May 12, 2007
Sleny: except, as I mentioned (and as is mentioned in the oracle docs), you can use INSTEAD OF triggers to redirect the insert/update/delete from the view to the actual table.
posted by sbutler at 8:45 AM on May 12, 2007
posted by sbutler at 8:45 AM on May 12, 2007
n/m, I misread your comment.
Looks like the best way to solve this problem is a new view.
posted by sbutler at 8:46 AM on May 12, 2007
Looks like the best way to solve this problem is a new view.
posted by sbutler at 8:46 AM on May 12, 2007
Yep, that was mentioned by another above and is certainly true.
The proper solution here is to modify the app to use Order By, but as it seems like that may not be an option for him, and as I reiterated first post, the view solution would work. (with the triggers)
posted by Slenny at 8:55 AM on May 12, 2007
The proper solution here is to modify the app to use Order By, but as it seems like that may not be an option for him, and as I reiterated first post, the view solution would work. (with the triggers)
posted by Slenny at 8:55 AM on May 12, 2007
Response by poster: Thanks all for your thoughts.
Yes - in general, I am aware of the proper solution - and am usually inclined to take the proper route.
However:
a) our development cycle is really long - think years, not months. I can't change that.
b) each customer wants different default sort orders, as specified in some admin settings.
There's enough info here to tell me not to bother messing with this. I was kind of hoping for ways of forcing Oracle to take a given route -- like some hidden override that would force a default sort order, and it isn't there. And of course, it shouldn't be, either.
The purist in me is satisfied. But my demo is fried too. Ah, well.
posted by blue_wardrobe at 12:08 PM on May 12, 2007
Yes - in general, I am aware of the proper solution - and am usually inclined to take the proper route.
However:
a) our development cycle is really long - think years, not months. I can't change that.
b) each customer wants different default sort orders, as specified in some admin settings.
There's enough info here to tell me not to bother messing with this. I was kind of hoping for ways of forcing Oracle to take a given route -- like some hidden override that would force a default sort order, and it isn't there. And of course, it shouldn't be, either.
The purist in me is satisfied. But my demo is fried too. Ah, well.
posted by blue_wardrobe at 12:08 PM on May 12, 2007
Best answer: There is really poor advice in this thread. I hope you guys don't manage databases for a living. The only way to do this without changing the application is to use ordered views. Ordered views on big tables can have severe performance penalties if the optimizer winds up sorting before it filters the rows. The optimizer is pretty smart and will keep from doing stupid stuff sometimes, but an ordered view is usually a bad idea on a table of any significant size.
posted by Lame_username at 1:46 PM on May 12, 2007
posted by Lame_username at 1:46 PM on May 12, 2007
Response by poster: No triggers needed either methinks.
posted by blue_wardrobe at 7:52 PM on May 13, 2007
posted by blue_wardrobe at 7:52 PM on May 13, 2007
This thread is closed to new comments.
posted by Blazecock Pileon at 9:22 PM on May 11, 2007