|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/29/2009 2:48:53 AM
Posts: 94,
Visits: 321
|
|
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,
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 8:47:37 PM
Posts: 283,
Visits: 989
|
|
|
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.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/22/2009 10:24:19 AM
Posts: 39,
Visits: 213
|
|
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.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/29/2009 2:48:53 AM
Posts: 94,
Visits: 321
|
|
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,
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/29/2009 2:48:53 AM
Posts: 94,
Visits: 321
|
|
HI,
Can anybody clear this please???
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/22/2009 10:24:19 AM
Posts: 39,
Visits: 213
|
|
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.
|
|
|
|