Archives of the TeradataForum
Message Posted: Wed, 26 May 2004 @ 18:58:38 GMT
| Subj: || || Re: Exclusion Joins |
| From: || || Dieter Noeth |
Anomy Anom wrote:
| ||I am trying to reproduce an exclusion join in a better fashion so that I can avoid overheads of redistributions and extra joins.|| |
You can rewrite it, but you'll probably have similar plans involving redistribution.
| ||I need to achieve all the rows from table X which do not exist in table Y and store them in Temp table.|| |
| ||Both tables have heavyweight indexes (X - 5 columns in UPI and Y - 7 columns in UPI)|| |
| ||The query does not involve all the UPI columns from either table and it might involve columns which are not part of UPI and are Nullable
INSERT INTO Temp SELECT * FROM table_X X WHERE X.i || X.j || X.k NOT IN
(SELECT Y.i || Y.j || Y.k FROM table_Y Y);
No need to concat all those rows:
where (i,j,k) in (select i,j,k from...)
| ||Another way could be using correlated query, but it will also be slow if both table have 5m rows.|| |
The overhead is the same as the multi-column subquery, because the optimizer rewrites both to the same plan.
| ||Isn't it possible to join them using Inner Join so that they produce exclusion join by adding some condition.|| |
[Inclusion] join is similar to an exclusion join, the first one is filtering on matching conditions, the second one on non-matching.
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
Maybe you should explain those variations, but IMHO they'll be quite similar due to query rewrite.