Archives of the TeradataForum

Message Posted: Wed, 26 May 2004 @ 15:39:42 GMT

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

I am not sure why you think a correlated subquery will be so slow, unless you are assuming this because of the slowness in other databases.

You might try the following, even via an EXPLAIN compared to your NOT IN that uses concatenation:

     INSERT INTO Temp
         SELECT * FROM table_X X
            WHERE NOT EXISTS(SELECT * Y.k FROM table_Y Y
                                where X.i=Y.i and X.j=Y.j );

You might also experiment with making both y.i and y.j secondary indices (if not already a PI) in an effort to speed it up even more. Because then, Teradata does not need to access the larger base data rows from Table_Y. Instead it can use the small index rows (more of them in a single block - fewer blocks to read - less IO=faster).

Hope this helps,

Michael Larkins
Certified Teradata Master

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