Is WITH (non recursive) a syntactic sugar?

Database

Is WITH (non recursive) a syntactic sugar?

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.
3 REPLIES

Re: Is WITH (non recursive) a syntactic sugar?

Does any TD expert have any clue?
Senior Apprentice

Re: Is WITH (non recursive) a syntactic sugar?

Hi timrick,
i usually got more efficient plans using WITH, especially if there's an aggregate or OLAP function.

I tried your query on similar data, when i removed the "where years = 1996" the optimizer re-used the WITH result set.

But GROUPING SETS with "HAVING mth IS NOT NULL OR yr = 1996" is better and it's always accessing the base table only once calculating the finest granularity first and then re-using that data.

Btw, be carefull when using QUARTER or MONTH, because this is not valid Teradata SQL and only works within QueryMan if the "Allow Use of ODBC SQL Extensions in Queries" option is set.

Dieter

Re: Is WITH (non recursive) a syntactic sugar?

Dieter, a belated thank you!

BTW, I turned on the ODBC parsing option in the teradata driver to get the ODBC syntax supported.