Authored by: Patrick Barel Viewed:
368 times so far
During my work for AMIS
I came across a (to me at least) new feature of the SQL engine. Instead
of building an inline view in the from clause of a statement you can
build this ‘inline view’ before the SQL statement and use the results
in the from clause. The syntax is pretty straightforward:
WITH < your_alias > AS ( < select_statement > )
SELECT < fieldlist >
FROM < tables, >< your_alias >
WHERE < where_clause > ;
It was quite puzzling to me at first, but I can definitely see
possibilities here. Not only is this approach probably more readable
than using inline views, but according to Oracle this approach is also
much faster.
Improving Query Performance with the SQL WITH Clause tells me a couple of things. First of all, this feature was introduced in Oracle 9
i
(which makes is apparent that I didn’t know of this feature, because 1)
I didn’t need it before and 2) my major customers at my previous
employer were still running Oracle 8
i). Second, this feature
is supposed to speed up query execution. If for instance you have
multiple references to the same query, you can of course copy and paste
it’s contents in the query, but that is not really Single Point Of
Definition. And the query has to be parsed and executed multiple times
where the result should be the same at all times. Of course you can
circumvent this issue by creating a real view in the database, but
there are times when you don’t want that, because you eiter don’t want
to expose the query logic, or you don’t have the rights to do this.
WITH Clause
File Attachments
User Comments 
Add a Comment
Related Article 
Language Translation
Search Knowledge Base