Derived Table vs View

Database

Derived Table vs View

Hi,

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.

Thanks

3 REPLIES
N/A

Re: Derived Table vs View

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?

N/A

Re: Derived Table vs View

Played arround with "WITH" syntax an seems that optimizer can not be cheated with this as well - which is good :->.

Re: Derived Table vs View

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
from table_a
) a (x, y)
inner join
table_b
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!