Views and Order By

Developers like views…they can simplify things and hide (protect us from) underlying complexity.

For example, would you rather write the query -

[sourcecode language="sql"]
SELECT
ename,
department_name
FROM
emp_vw
[/sourcecode]

or

[sourcecode language="sql"]
SELECT
e.ename,
d.dname
FROM
emp e,
dept d
WHERE
e.deptno = d.deptno;
[/sourcecode]

It’s a rhetorical question so please don’t say “I prefer the second”…otherwise there’s no point me continuing this post. Anyway, that’s quite a simple example, imagine the view hides all sorts of complexity (analytics, subquerys etc).

The view gives us a single ‘point of truth’, we can tell developers “use that view, it contains all the information you need, you don’t need to understand all the logic yourself”, views are a very powerful tool for code re-usability (and simplification).

However, what if someone codes a view like this -

[sourcecode language="sql"]
CREATE OR REPLACE VIEW empdept_vw AS
SELECT
e.ename,
d.dname,
e.sal
FROM
emp e,
dept d
WHERE
e.deptno = d.deptno
ORDER BY d.dname;
[/sourcecode]

Notice the order by there? They probably/possibly created that view to use directly in a report, adding the ORDER BY into the view so they could just write -

[sourcecode language="sql"]
SELECT * FROM empdept_vw
[/sourcecode]

rather than

[sourcecode language="sql"]
SELECT * FROM empdept_vw edv ORDER BY edv.dname
[/sourcecode]

Well that’s fine, but what is someone else later on uses that view in this way -

[sourcecode language="sql"]
SELECT * from empdept_vw edv ORDER BY edv.sal DESC
[/sourcecode]

Well we’re now potentially making the database do more work because the query is sorting the data in a different way to the view sorts it (so therefore the sorting in the view is essentially redundant). The key point here is that the database cost based optimizer might be smart enough to figure out what you’re doing and eliminate the redundant sort, however there might be lots of cases where that doesn’t happen (consider the scenario where you have views built on top of views, with ORDER BY’s thrown in there at a deep level).

The cost-based optimizer gets smarter with every release of the Oracle datbase, however it will never (and I’m reasonably confident about this!) understand your data and requirements as much as you do (or rather as much as you should).

So, I would argue anytime you see a view with an ORDER BY in it you should really question why you’re doing that (rather than putting the ORDER BY in the query that uses the view). We can even make our job easier and look for any views that have an ORDER BY defined in them by querying DBA_VIEWS or USER_VIEWS. Unfortunately those views contain the text definition of the view in a LONG column rather than a CLOB, which means we can’t do a simple LIKE predicate -

[sourcecode language="sql"]
JES@dbtest> select * from user_views where upper(text) like ‘%ORDER BY%’;
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
[/sourcecode]

We can get round this in a number of ways (such as creating a copy of the DBA_VIEWS or USER_VIEWS in a local table converting the LONG column to a CLOB etc), or we can use a different approach like this -

[sourcecode language="sql"]
JES@dbtest> select
*
from
user_views
where
dbms_xmlgen.getxml(‘select text from user_views where view_name = ”’ || view_name || ””) like ‘%ORDER BY%’
[/sourcecode]

(note unfortunately I can’t remember who to credit with the DBMS_XMLGEN trick above, I saw it a long time ago and it’s been lost in the mists of time).

and yes…before you ask…in the schema I ran that query in I did find a view that had an unnecessary ORDER BY in ;)

I’d be interested to hear your thoughts on if you find ORDER BY’s in views a problem/issue/area-of-concern, or if it’s just me?

4 responses

  1. In my humble opinion a view should never, ever contain an order by, unless off course it’s the order by clause of an analytical function.

    Even if you have a clear and limited use for the view the moment you create it, you will simply not ever be able to guess all the uses other developers will think of in the (near) future.

    Alltough an order by can be a technical demand for a use of the view it’s still the demand for THAT specific use of the view.
    Like Martin pointed out, an order by is for presentation purposes.

    So I think there’s simply no excuse for order by in a view. It’s for (very) lazy developers who do not want to type it with every query based on that view.

Leave a Reply

Your email address will not be published. Required fields are marked *