Archives of the TeradataForum

Message Posted: Wed, 26 May 2004 @ 18:58:38 GMT


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

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 fields

     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.

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
     ;

Maybe you should explain those variations, but IMHO they'll be quite similar due to query rewrite.

Dieter


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