Archives of the TeradataForum

Message Posted: Tue, 26 May 2004 @ 01:43:33 GMT

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

<-- Anonymously Posted: Tuesday, May 25, 2004 11:19 -->

Hi,

I am trying to reproduce an exclusion join in a better fashion so that I can avoid overheads of redistributions and extra joins.

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);

Another way could be using correlated query, but it will also be slow if both table have 5m rows.

Isn't it possible to join them using Inner Join so that they produce exclusion join by adding some condition.

Regards.

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