March 11, 2005
Order By and Oracle View Definitions
A question came up at work last week, and the answer wasn't present in the documentation or readily available when I checked my backup brain.
The question? "Why can't I add an order by clause to my CREATE VIEW statement?"
Luckily my many years of experience and knowledge of arcane trivia came to my aid in helping me form an answer. This answer involved the relational algebra and some basic theories of relational databases. Some knowledge that I worryingly find isn't well spread amongst my fellow IT professionals. So I thought I would share it with you dear reader.
The unit of operation in a relational database is a relation. For those of us seperated by a number of years from serious mathematical study a relation is a collection of similar items. According to this definition (and I'm asking you to trust me on this) a relation isn't ordered. You can apply an order to a relation after you have retrieved it. In a relational database a table is a representation of a relation, the results of any operation are a relation (for instance a query result set is a relation which is created on the fly), and a view is a relation which is assembled on request.
Whilst it's probably technically possible to order a view the nice people at Oracle don't support it because it breaks the spirit of Codd's rules. More practically they don't allow you to order a view because it is likely be a performance problem. To apply an order by to a query result you first to have fetch all of the data, and then order it. Without an order by clause you can just initially fetch as many rows as you like and then subsequently fetch more and more rows as they are required.
Other databases may vary, MySQL doesn't support views, at least not in the version I've got installed, I don't have Microsoft SQL Server, Ingres, Firebird, DB2 or PostgreSQL installed on any machine I have access to. If you do have any of these databases and want to pitch in leave a note in the comments.
For more reading, as ever, wikipedia is your friend. In addition to the sections I've already linked to there is a comprehensive overview of the relational model and hardcore maths nuts can read about the relational calculus.
Posted by Andy Todd at March 11, 2005 09:34 PM
I tend to think of views as being exactly the same as a table. Views therefore can't be ordered in the same way as a table can't be ordered. If you want the data from a table ordered, you ORDER BY in the select; same with a view...
Posted by: Stuart Langridge on March 11, 2005 09:07 PMStuart? Talking about databases?
Anyway, I'm not sure where exactly where you want the order by clause, but
create view testorder as select * from emp order by ename;
works for me.
Are you trying to do something different (note I've only tested this on 9.2.0.6
Interbase (so I assume Firebird too) don't support ordering in view definitions. On the other side, MSSQL2k allows ordering if you specify count of returned rows (even as "SELECT TOP 100 PERCENT").
Anyway, I found little use for such views in my career.
SQL Server and UDB2/400 both have views. You can't order them, as is only right and proper.
Posted by: Simon Brunning on March 11, 2005 11:44 PMTheoretically, databases are rooted in relational theory. And theoretically, it doesn't matter how you write a query because the optimiser will work out the best way to execute it.
Practically speaking, theory is hopelessly wrong. Bring on CREATE VIEW with ORDER BY!
Posted by: Alan Green on March 12, 2005 07:47 PM