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