All Forums Database
ashikmh 11 posts Joined 02/12
08 Mar 2013
Is it good to creating SET table with UPI or NUPI and why..?

Can you please help me to understand on SET tables having  UPI and NUPI. What will be the difference between them.
Thanks,

AdamL 5 posts Joined 01/13
09 Mar 2013

The below link might help u.
http://forums.teradata.com/forum/database/set-with-upi

09 Mar 2013

Hi
Its better to create a SET table with a UPI or enforce uniquness on atleast one of the columns
if not on the PI columns.
SET tables dont allow duplicates however if you are deifining a NUPI then it allows duplicates on PI columns
which means ever time data is loaded its not enough to perform duplicates check on the PI columns alone
it has to perform the check for the entire row set, which is time consuming
Instead if you define another column as Unique then it needs to check only on that column.
 
Regards
R.Rajeev
 
 

AB75151 20 posts Joined 06/09
10 Mar 2013

SET table with UPI is always better because it enforces the uniquencess of the data in turn it ensures that data is distributed across AMPS evenly. A evenly distributed data improves the performance of the retrieval.
As Rajeev rightly mentioned, if you define a NUPI, then the SET operation needs to scan the entire row ensure that there are no row duplicates. Hope this helps.

Adeel Chaudhry 773 posts Joined 04/08
11 Mar 2013

What is going to be the use of the table? What type of data is expected? Is it going to be History Handled? Is it going to be loaded near real-time? Will it be used in joins majority of the times? Whats the expected data volume?
 
Your question's answer depends on all these questions. It is not a common rule that you always need to have a unique PI .... there are countless scenarios where NUPI is the only solution based on usage of the table.

-- If you are stuck at something .... consider it an opportunity to think anew.

macktd 23 posts Joined 09/12
13 Mar 2013

Please give me one example on three tables to join, using Scalar subqueries.
Regards,
mack

You must sign in to leave a comment.