If I create a view (call it B ) and use that in another SQL (call it A), would that work different from using a derived table?
I'm evaluating this for performance reasons, but I'm not sure which one would be better. Basically, I need to be able buffer the result of B and use that in A. When I tried using B as View and looked at the explain plan, I noticed that Teradata simply expanded that view and merged it with A. I want to make sure that B is processed seperately without merging with A, and then join with A. In other words I want know if I can get the affect of using a VT/GTT (for B) without actually using VT/GTT.
A view is "only" a logical construct for the optimizer. He will always resolve it to the base tables - also over many nesting levels.
Derived tables often also be rewritten as the optimizer is quite smart...
Did you try the with syntax?
Thanks very much Ulrich. I think your suggestion using "WITH" clause works in my case.
I'm just wondering why a name "Derived table" given - to me it just seems like a subquery (used in FROM clause).
select * from
(select x, y
) a (x, y)
on a.x = table_b.x
In the above derived table "a", what is the significance of "a(x, y)"? What if I don't expose "x" and "y" explicitly - if it is just for aliasing, we can even do that within subquery (like "select x as x_new, y as y_new from table_a"). I was under the impression that "a (x, y)" has special purpose and Teradata truly buffers table "a" in buffer/spool (like a volatile table) and uses the result in rest of the joins. Can you help me understand? Thanks!