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 -

SELECT
  ename,
  department_name
FROM
  emp_vw

or

SELECT
  e.ename,
  d.dname
FROM
  emp e,
  dept d
WHERE
  e.deptno = d.deptno;

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 -

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;

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 -

SELECT * FROM empdept_vw

rather than

SELECT * FROM empdept_vw edv ORDER BY edv.dname

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

SELECT * from empdept_vw edv ORDER BY edv.sal DESC

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 -

JES@dbtest> select * from user_views where upper(text) like '%ORDER BY%';
                                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

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 -

JES@dbtest> select
  *
from
  user_views
where
  dbms_xmlgen.getxml('select text from user_views where view_name = ''' || view_name || '''') like '%ORDER BY%'

(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?

Comments 4

  1. Tony Andrews wrote:

    John,

    Totally agree with you; in fact I was dealing with such a case recently where a developer expected the view’s ORDER BY clause to be honoured even when the query using it had a different ORDER BY! I asked Tom Kyte for his views on this here:

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2143982000346585885#2377754100346873409

    Posted 25 Feb 2010 at 12:42 pm
  2. Martin Giffy D'Souza wrote:

    Hi John,

    I agree 100%. To me, views encapsulate the business logic. Order by is part of the presentation layer and shouldn’t be placed in the view definition.

    Nice trick on the xmlgen!

    Martin

    Posted 25 Feb 2010 at 4:41 pm
  3. Scott Wesley wrote:

    Exactly what Martin said ;-)

    Scott.

    Posted 26 Feb 2010 at 5:32 am
  4. Erik van Roon wrote:

    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.

    Posted 26 Feb 2010 at 11:57 pm

Post a Comment

Your email is never published nor shared. Required fields are marked *