SET with UPI

Database
Enthusiast

SET with UPI

I am stuck with a strange situation ....I am having about 100 tables in my system which are

SET+UPI defined upon that
SET----system level check to ensure no duplicate rows are there
UPI----table level check to ensure a particular column has no repeated values thus identifying a row uniquely

My doubt is that will SET+UPI combination will do duplicate row checking or only single time checking will be done?

Also what is the process that really goes
i mean to say 1st SET is checked then UPI check is done or it's vice versa...
Please clarify as i need to change all 100 tables if SET+UPi is a problem...Also please clarify how really SET checking and UPI checking is done by Teradata while inserting data

Regards,
5 REPLIES
Enthusiast

Re: SET with UPI

If a table has any unique index (primary or secondary) there is never any duplicate row checking. Checking for duplicate rows would be redundant in that case, so the Teradata software recognizes that it is not necessary.
Junior Supporter

Re: SET with UPI

This may be not true for INSERT...SELECT's. If there is an UPI on a SET TABLE, Teradata silently removes duplicates BEFORE the INSERT and there will be no 'Duplicate unique prime key error' (thus the 'duplicate checking' occurs no matter if there is an UPI or not).

It is explained here (in spanish) http://carlosal.wordpress.com/2009/02/16/tablas-set-y-multiset-en-teradata/

HTH

Cheers.

Carlos.
Enthusiast

Re: SET with UPI

Hi Carlos,
I am bit confused as which one of you is saying correct ,also i was unable to understand anything as all in spanish
Could you please write something from that extract in english explaining how really teradata checks SET command i.e mechanism involved in it as it is important for me to present all aspects in front of team to remove those 100 tables from SET to MULTISET

Regards,
Enthusiast

Re: SET with UPI

HI,
Can anybody clear this please???
Junior Supporter

Re: SET with UPI



1.- Duplicate row check works different with INSERT...SELECT (remove duplicates silently) and INSERT INTO...VALUES (raise errors)

2.- If there is a UNIQUE restriction on a SET table (UPI, USI...) Teradata uses this restriction (less costly) instead of the 'SET' duplicate row check.

More information here (in spanish):

http://carlosal.wordpress.com/2009/02/16/tablas-set-y-multiset-en-teradata/
http://carlosal.wordpress.com/2009/07/06/tablas-set-y-multiset-en-teradata-ii/

(Google translate is your friend...)

HTH.

Cheers.

Carlos.