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