Archives of the TeradataForum

Message Posted: Fri, 15 Oct 2004 @ 15:52:24 GMT

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

Hi:

You might try a correlated subquery as a method to speed up the query and eliminate the duplicate row checking in the UNION by using ALL (assuming no dups):

     ins into table_copy
       sel * from mtable
     UNION ALL
       SELECT * FROM table  AS tbl
        WHERE NOT EXISTS (sel * FROM Mtable
                             where tbl.X=X and tbl.Y=y);

This can be particularly fast if x,y can be defined as a USI in Mtable because it would not need to use the base table, only the index blocks to make this determination.

Someone did ask about the PI of these tables. It can make a difference if redistribution of the inserted data is required. However, the rows will need to be selected anyway. A bigger difference would be regarding the original condition of table_copy. If it is empty at the start, there is no need for Teradata to use the Transient Journal for each row inserted - huge performance increase.

Just a couple thoughts and hope it helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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