Archives of the TeradataForum

Message Posted: Mon, 23 Jul 2012 @ 11:07:36 GMT


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

Subj:		Re: Insert statement starts OK but then slows down

From:		Frydryszak, Marek

SET --- UPI ---- No duplicates allowed, but no need to do a duplicate row check, only PI data is checked for duplicate values.

It seems to be true but not the whole true !

Let's analyze such example:

     create volatile set  table upiset ( n integer, c char(1) ) unique primary
     index ( n);
     insert into upiset
      select  a,c from ( sel 1 a, 'X' c ) t1
       union all
      select  a,c from ( sel 1 a, 'X' c ) t2
     ;

     result : INSERT completed. 1 rows processed (not an error !!!)

It means, than - if rows are inserted in one statement - TD first compares the whole row and in case of double silently eliminates second and next identical rows instead of generating error 2801 (Duplicate unique primary key).

I believe, that actually algorithm is better:

- first check if PI columns are unique

- if not, compare the whole row and if such row exists ignore it.