Can you please help me to understand on SET tables having UPI and NUPI. What will be the difference between them.
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.
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.
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.