The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as an inline view. You can force the inline view to a temporay table by using the unsupported hint /*+ MATERIALIZE */


with MyQuery1 as (select /*+ MATERIALIZE */ MyCol1 from MyTable1)
select * from MyQuery1 a, MyTable2 b where a.MyCol1=b.MyCol2
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License