{"id":219,"date":"2008-01-25T17:30:26","date_gmt":"2008-01-25T16:30:26","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=219"},"modified":"2008-01-25T17:30:26","modified_gmt":"2008-01-25T16:30:26","slug":"with-clause","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=219","title":{"rendered":"WITH Clause"},"content":{"rendered":"

During my work for AMIS<\/a> 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 \"9ifinal\"<\/a>clause. The syntax is pretty straightforward: <\/p>\n

\n
  <\/span>WITH<\/span> <\/span><<\/span>your_alias<\/span>><\/span> <\/span>AS<\/span> (<\/span><<\/span>select_statement<\/span>><\/span>) \n<\/span>SELECT<\/span> <\/span><<\/span>fieldlist<\/span>><\/span> \n  <\/span>FROM<\/span> <\/span><<\/span>tables,<\/span>><<\/span>your_alias<\/span>><\/span> \n <\/span>WHERE<\/span> <\/span><<\/span>where_clause<\/span>><\/span>;\n<\/span><\/div><\/pre>\n
<\/div>\n
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. <\/p>\n
<\/p>\n
 Improving Query Performance with the SQL WITH Clause<\/a> tells me a couple of things. First of all, this feature was introduced in Oracle 9i<\/em> (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 8i<\/em>). 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.<\/p>\n
Consider the following query (borrowed from Ask The Oracle Expert<\/a>):<\/p>\n<\/p>\n
\n
select<\/span> product_id\n     , supplier\n     , price\n     , iteminfo\n  <\/span>from<\/span> (\n       <\/span>select<\/span> ... <\/span>from<\/span> table1\n       <\/span>union<\/span> <\/span>all<\/span>\n       <\/span>select<\/span> ... <\/span>from<\/span> table2\n       <\/span>union<\/span> <\/span>all<\/span>\n       <\/span>select<\/span> ... <\/span>from<\/span> table3\n       ) <\/span>as<\/span> T\n <\/span>where<\/span> price <\/span>=<\/span>\n       ( <\/span>select<\/span> <\/span>min<\/span>(price)\n           <\/span>from<\/span> (\n                <\/span>select<\/span> ... <\/span>from<\/span> table1\n                <\/span>union<\/span> <\/span>all<\/span>\n                <\/span>select<\/span> ... <\/span>from<\/span> table2\n                <\/span>union<\/span> <\/span>all<\/span>\n                <\/span>select<\/span> ... <\/span>from<\/span> table3\n                ) <\/span>as<\/span> T1\n          <\/span>where<\/span> product_id <\/span>=<\/span> T.product_id )<\/span><\/div><\/pre>\n
<\/div>\n
    
and consider the same query, but now using the WITH clause: <\/p>\n
<\/p>\n
\n
with<\/span> simpletable <\/span>as<\/span>\n(\n<\/span>select<\/span> ... <\/span>from<\/span> table1                 \n<\/span>union<\/span> <\/span>all<\/span>                              \n<\/span>select<\/span> ... <\/span>from<\/span> table2                 \n<\/span>union<\/span> <\/span>all<\/span>                              \n<\/span>select<\/span> ... <\/span>from<\/span> table3                 \n)\n<\/span>select<\/span> product_id\n     , supplier\n     , price\n     , iteminfo\n  <\/span>from<\/span> simpletable <\/span>as<\/span> T\n <\/span>where<\/span> price <\/span>=<\/span>\n       ( <\/span>select<\/span> <\/span>min<\/span>(price)\n           <\/span>from<\/span> simpletable\n          <\/span>where<\/span> product_id <\/span>=<\/span> T.product_id )<\/span><\/div><\/pre>\n
<\/div>\n<\/p>\n
The second query is not only more readable, but it also implements SPOD<\/a>. If I need to change the ‘inline view’, then I should make sure I make the same changes in both (or maybe even more) queries.<\/p>\n
According to Don Burleson in his article Oracle SQL-99 WITH clause<\/a> the following apply:<\/p>\n