Archives of the TeradataForum

Message Posted: Wed, 12 Sep 2007 @ 11:17:28 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: In WHERE clause multiple equality conditions ORed

From:		Dieter Noeth

Anomy.Anom wrote:

In the following SQL statement WHERE clause has multiple equality conditions ORed and Explain plan shows PRODUCT join. When we execute this query it is spooling out or runs for very long.

Is there any alternative way we can write this query to avoid PRODUCT join.

Rows to be joined must be on (or sent to) the same AMP, but in your case there might be more than one join to a singe row, so the optimizer can't decide to which AMP the row has to be sent. And then it's always using a product join.

But in many cases an OR-ed join condition like yours is not a logical OR but an XOR:

Is only one of those A.COL1/2/3 <> ''?

Is only one of those B.COL1/2/3 <> ''?

If A.COL2 <> '' is B.COL2 <> '', too?

If those conditions are true then it can be replaced by CASE/COALESCE.

     CASE
        WHEN A.COL1 <> '' THEN A.COL1
        WHEN A.COL2 <> '' THEN A.COL2
        WHEN A.COL3 <> '' THEN A.COL3
     END =
     CASE
        WHEN B.COL1 <> '' THEN B.COL1
        WHEN B.COL2 <> '' THEN B.COL2
        WHEN B.COL3 <> '' THEN B.COL3
     END

Still looks complicated, but now the optimizer knows which column to use for hashing/joining.

And the last condition looks like a kind of Outer Join.

If it's not an XOR you'll have to split the query to several UNION ALLs.

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference