All Forums Database
karteeky 7 posts Joined 02/11
08 May 2012
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

ulrich 816 posts Joined 09/09
09 May 2012

 

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?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
09 May 2012

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

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

karteeky 7 posts Joined 02/11
09 May 2012

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!

You must sign in to leave a comment.