Archives of the TeradataForum
Message Posted: Sat, 29 May 2004 @ 08:51:36 GMT
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 ;
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...
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 ;-)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|