Sub-Query Join and Union Problem

Database

Sub-Query Join and Union Problem

If someone could please help, basically I can't optimize my query cause I want to join on 3 union tables – which can’t “see” within scope I guess, what came before:

My real code is far more complex but it shows the problem. I wanted to just make a big volatile table but this database is huge, static date ranges work but its so slow! Its just a pain cause these 3 tables are nearly identical but making the join on a 3 union select seems to be spool heavy. If it was 1 table, it would just be 1 join. Yes I could do the whole thing 3 times and union, but this is just a piece of a much larger situation. Thanks!

Select

Users.User ,
Users.MTN ,
AddressTables.Address
from Users

/*Very slow it must be unioning all the rows into spool before linking *?

Left Outer Join (

Select Address from Table1 where date between “this month, etc”
UNION
Select Address from Table2 where date between ….
UNION
Select Address from Table3 where date between ….
) AddressTables ON

AddressTables.MTN=Users.MTN

*****Here;s what I want but it doesn’t work, it cant see the “scope” and errors out

Select

Users.User ,
Users.MTN ,
AddressTables.Address
from Users

/*Very slow it must be unioning all the rows into spool before linking *?

Left Outer Join (

Select Address from Table1 where Users.MTN= Table1.MTN (THIS WOULD ROCK BUT DOESN’T WORK! Error Obj Users does not exists?)

UNION
Select Address from Table2 where Users.MTN=Table2.MTN and date between ….
UNION
Select Address from Table3 where Users.MTN=Table3.MTN date between ….
) AddressTables ON

AddressTables.MTN=Users.MTN

4 REPLIES
Senior Apprentice

Re: Sub-Query Join and Union Problem

Hi Tim,
first of all you should check if you can replace the UNION with a UNION ALL.
UNION is automatically DISTINCT, which is a huge overhead.

This should help already.
If it's still not fast enough please post the DDL, statistics and EXPLAINs.

Btw, what's your Teradata release?

Dieter

Re: Sub-Query Join and Union Problem

Hi there much appreciated, in fact that gave me about a 25% savings in time alone. This is the version Im working from :

VERSION 06.02.02.56
RELEASE V2R.06.02.02.56

I have some work guidlines around code postings (that im not exactly sure of) but anyhow my example shows the basic problem I've had before. A Join onto a nested table (created subquery) does seem to prevent any kind of linking until the On Statement. It's like Im forced to use static values (date ranges, etc) inside that whole section (no using outside predicates)

Thanks for your time

Enthusiast

Re: Sub-Query Join and Union Problem

Hi Tim,

This could be better:

Select
Users.User ,
Users.MTN ,
Table1.Address
from users left outer join Table1 ON

Table1.MTN=Users.MTN
where date between “this month, etc”

union

Select
Users.User ,
Users.MTN ,
Table2.Address
from users left outer join Table2 ON
Table2.MTN=Users.MTN
where date between “this month, etc”

union

Select
Users.User ,
Users.MTN ,
Table3.Address
from users left outer join Table3 ON
Table3.MTN=Users.MTN
where date between “this month, etc”

Could you let me know if this helpful

Re: Sub-Query Join and Union Problem

Thanks for the reply, you are very correct that is better (in theory). Problem is, the actual Query is so large - duplicating it 3 times could cause problems down the road keeping them all in sync. It's just a strange but simple problem.. I just wonder even if I could reference the main query variables, if it would save spool space anyway