Archives of the TeradataForum

Message Posted: Sat, 29 May 2004 @ 08:51:36 GMT


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

Subj:		Re: Exclusion Joins

From:		Dieter Noeth

Christie, Jon wrote:

     Another query:
     INSERT INTO Temp
     SELECT * FROM table_X X LEFT JOIN table_Y Y
     ON X.i = Y.i AND X.j = Y.j AND X.k = Y.k
     WHERE Y.i IS NULL
     ;

Dieter, I'd run an explain on this one. You're absolutely right that this version of the query will return the same result, but it may not get a good plan.

Not only a less efficient plan, probably even a different result set: The OP talked about joining on a subset of the PIs, so it's a m:n join maybe retrieving the same rows several times. If the target table is Set those duplicates will be silently removed, but if it's Multiset...

I have seen a case where the optimizer chose to do the outer join and then remove the inner join rows from the result by running the spool though a retrieve step. In almost all cases, this would be a slower plan than using an exclusion join. At some versions, the optimizer may be smart enough to use an exclusion join, but if it doesn't, you've got plan that will not perform well.

Of course you're right, the optimizer will always (even in V2R5.1) do an extra retrieve step "where col is null". The only advantage might be avoiding the Distinct step of the subquery, but not in this case.

The Not Exists version is probably the most efficient one, especially if there are any Nullable columns involved...

But at least those queries show the OP that it's impossible to avoid the redistribution he was trying to achieve ;-)

Dieter


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

Attachments

Library

Quick Reference