Hello, let's say we have this query.
FROM table_1 A
INNER JOIN table_2 B
ON B.clm3 ='xxxx'
AND (A.clm1=B.clm1 OR B.clm1 = 'xxxx' )
How can we rewrite it by replacing the or operator?
Because TD makes a horible plan.
Is it possible with a left Join.
Thank you very much for your time.
It's a horrible plan, because it's a horrible query :-)
Your query asks for a kind of Cross Join, there's no way to avoid the product join using a Left Join.
Usually an ORed join condition is replaced by UNION ALL, but your query is really strange, could you please elaborate what you actually want to achieve?
To be honest , I am not the one who wrote this strange query , they just gave me to see if i can tune it.
I was informed that it has been produced by a tool . I am still trying to understund their business logic.
Where I conclude,was that even a union is not helping,just this syntax (according always to the kind of data they have)
AND A.clm1=CASE WHEN B.clm1='*' THEN A.clm1 ELSE B.clm1 END