How to eliminate OR clause when joining tables?

Applications Programming
1

HI Team

I have q tricky question when joining 3 tables in Teradata DB.In the last line of my query , I am comparing one condition with C an B tables and its creating performance issue in returning the data . Basically I want to eliminate OR clause and try other approach.Is there any logic to satisfy this condition . Thanks in advance

[SEL * FROM 
( SEL ORD_ID,NUM ,DATE 
from TABLE1 ) A
ON ORD_ID is NOT NULL
LEFT JOIN 
(
SELECT ORD_ID,NUM,DATE ,Contract_ID
FROM TABLE2) B
ON ORD_ID is NOT NULL
LEFT JOIN 
( SELECT ORD_ID,NUM,DATE ,Contract_ID
FROM TABLE3 ) C
ON (A.ORD_ID=C.ORD_ID
AND A.NUM=C.NUM)
OR ( C.Contract_ID=B.Contract_ID)
]
2

I don't know Teradata syntax, but maybe you could split up that query into two parts: (1) join "a" and "c" and (2) join "b" and "c". Then combine the results.

3

It looks like you're doing a correlated subquery. Those are nasty performance killers.

Could you post the columns available in each table, the relationships between the tables, and the results you're trying to get?

1 Like
4

Hmm that is nasty - here is a generalized discussion of the problem:

https://en.wikipedia.org/wiki/Correlated\_subquery\#Optimizing\_correlated_subqueries

1 Like