Hi, I would like to know if an intermediate table defined by a non-recursive WITH is ever persisted or its intermediate rowset ever shared during the query execution, or will it always behave like an inline view?
I have queries that have multiple leaf blocks that perform the star join from the same set of tables. I have tried using WITH clause to extract out the star join portion hoping that it would be done only once; but examining the Explain, I see the WITH clause got plugged back.
Below is a simple example I tried on V2R6.1
WITH commontable1 (quarter, year, revenue) as ( select quarter(T3285.OrderDate), year(T3285.OrderDate), sum(T3279.UnitPrice * T3279.Quantity) from OrderDetails T3279, Orders T3285 where T3279.OrderID = T3285.OrderID group by quarter(T3285.OrderDate), year(T3285.OrderDate) )
(select sum(revenue) as c1, cast (NULL as int) c2, years as c3 from commontable1 group by years) union all (select revenue as c1, qtrs as c2, years as c3 from commontable1 where years = 1996)
I know I can rewrite the above query with RollUp or Grouping Sets, even though I am not certain if different level of aggregations are computed from the same intermediate rowset.