Archives of the TeradataForum

Message Posted: Mon, 23 Jul 2012 @ 17:08:44 GMT

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

Hi,

From discussions with Dieter and some other notes that I've now seen: a USI will NOT prevent the duplicate row check - despite what the manual says (my understanding is that the manual is being corrected).

Looking at your examples from below:

SET --- UPI ---- duplicates will not be allowed double checks each row?

I'm not sure I'd describe this as a 'double check' because remember what limits the number of rows that are checked for duplicates. The system only checks those rows which have the same rowhash value, i.e. only those rows where the PI data value generates the same rowhash. Given that the unique index in this example is the PI, I don't see this as a double check.

SET --- PI ---- duplicates will not be allowed, no double checks?

Correct. Only 'duplicate row check' is used as there is no unique index.

MULTISET --- UPI ---- Duplicates will not be allowed, no double check for each row?

Because of the MULTISET there is not going to be any 'duplicate row check', so only the single check.

MULTISET --- pi ---- Duplicates will be allowed, no double checks?

Again, no 'duplicate row check' because of the MULTISET and as there are no unique indexes then there is NO checking for any duplicates.

One area that you didn't cover is when the pi is NUPI and there is a USI. So using the same nomenclature as above:

SET - NUPI - USI: this is the bit that I referred ot above as being wrong in the manuals. It appears that the USI does NOT prevent the duplicate row check. According to som e'internals' information that I've seen, the row is always inserted first and so the 'duplicate row check' is done to see if it is a dup row or not. THEN the index entry is added - which of course may also give a uniqueneess violation.

MULTISET - NUPI - USI: no 'duplicate row check' because of the MULTISET but when the index entry is added that may generate a uniqueness violation.

I hope this also answers Marek's question/point.

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

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