All Forums Database
Robinhood 7 posts Joined 09/11
18 Oct 2015
Product Join in Teradata 15

Hi Dieter ,
I am running below query 
SELECT ABC.COL1,
ABC.COL2,
ABC.COL3
FROM 
DATABASENAME.ABCD ;
ABC is also tablename which exists in same database where ABCD resides.I am running above query in SQLA 15.0 .First of all , it is not getting failed .I am totally got surprised  to see this behaviour and other thing if i am looking at explain plan then it is making product join between the tables ABC and ABCD .
I am not sure about this functionality at all .
 
Thanks,
RS

Tags:
oshun 43 posts Joined 07/10
18 Oct 2015

As far as I remember this behaviour is the same like in previous releases and not related to Teradata 15. Actually, one of the "features" which can lead to errors whcih are very difficult to detect. 
BR
Roland
 

visit my private blog at http://www.dwhpro.com

ToddAWalter 316 posts Joined 10/11
18 Oct 2015

Since the early days of Teradata, the grammar has not required the FROM clause. early on this was viewed as an ease of use feature. The parser looks through the query finding all database and table references and adding them to the internal version of the FROM clause. It then satisfies column references from that list and uses that list as the list of tables to be joined. While it is now recognized that the ease of use is outweighed by the issues caused by surprises  like you describe above, it is hard to change it at this point because there are a lot of queries out there that would fail because they count on this legacy behavior.

Robinhood 7 posts Joined 09/11
18 Oct 2015

Hi Roland/Folks,
Do you think is the right behaviour as database perspective? I am running same query through Informatica .I was expecting error but it did not get failed .Is there any way or setting we can set for failing of this query ?

Thanks,
RS

ToddAWalter 316 posts Joined 10/11
18 Oct 2015

There is not a way to cause this behavior to change and issue an error instead.
 
If the product join is large, it is possible to catch it with a TASM filter and error it because of the combination of product join and very large row/cost estimate.

Robinhood 7 posts Joined 09/11
08 Dec 2015

Hi ,

I am facing peformance issue whle running query .Below logic is implemented in view .

 

SELECT 

*

FROM

table A

 

WHERE

(A.c, A.d) in (

SELECT  x, y

FROM

table b

where  user1 = USER

group by 1,2

)

OR (A.c, A.e) IN (

select x, y

from c

where user1= user

group by 1,2

 

);

 

 

could you please help out for same how we can rewrite same in better 

Srinivas_tera 5 posts Joined 12/11
10 Dec 2015

The below approach will improve the performance ...
SELECT * FROM  table A WHERE (A.c, A.d) in
 (SELECT  x, y FROM table b where  user1 = USER group by 1,2 )
UNION ALL
SELECT * FROM  table A WHERE (A.c, A.e) IN
 ( select x, y from cwhere user1= user group by 1,2);

-S

You must sign in to leave a comment.