Is it good to creating SET table with UPI or NUPI and why..?

Database
Enthusiast

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,

5 REPLIES
Enthusiast

Re: Is it good to creating SET table with UPI or NUPI and why..?

Enthusiast

Re: Is it good to creating SET table with UPI or NUPI and why..?

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

Enthusiast

Re: Is it good to creating SET table with UPI or NUPI and why..?

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.

Teradata Employee

Re: Is it good to creating SET table with UPI or NUPI and why..?

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.

Enthusiast

Re: Is it good to creating SET table with UPI or NUPI and why..?

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

Regards,

mack