Archives of the TeradataForum

Message Posted: Wed, 13 Oct 2004 @ 11:45:40 GMT


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

Subj:		Re: Can this query be written more efficient

From:		Victor Sokovin

Basically trying to insert into a copy tables all rows from table (except the ones that exist in mtable) + insert into table copy all the rows from mtable.

          INSERT INTO table_copy
          SELECT *
          FROM table
          WHERE (X,Y) NOT IN (SELECT DISTINCT X,Y
                              FROM Mtable)
          ;insert into table_copy
          select *
          from Mtable;

Can this query be written in a more efficient manner. It currently takes 4 hours with table, 61+ millions rows and mtable, 20+ thousands rows.

Are the PI on Table and Table_copy the same?

You don't really need the DISTINCT and might try to drop it, if it can help the optimizer, although it should not really matter with the table of 20+ thousands rows.

Regards,

Victor