Archives of the TeradataForum

Message Posted: Wed, 13 Oct 2004 @ 14:07:19 GMT

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

Hi,

It would help if we could get the explain plan from the original running query. I assume that the table_copy, table and Mtable all have the same PI, if not it would help to know this and why not.

My understanding of this, is that you always want the records contained within Mtable to go through.

You mention that Mtable only contains 20,000 records. But what is the relationship between this table and Table. Is it 1:1, can we only expect that 20,000 records from Table need to be excluded.

If it is 1:1, then have you tried a 3 step process.

For example:

     INSERT INTO table_copy
     SELECT * FROM table;

     DELETE FROM table_copy
     WHERE (X,Y) IN (SELECT DISTINCT X,Y
                     FROM Mtable);

     INSERT INTO table_copy
     SELECT * FROM Mtable;

Yes I do realise that you will get logging happening for steps 2 and 3, but with the small number of rows (assuming 20,000) it could be alot more performant. Especially with all the recent improvements being made in 5.x and 6.x

Just a thought, but worth testing.

Regards

Matthew Winter

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