why should we use SET table ?

Junior Contributor

Re: why should we use SET table ?

Hi Sughesh,

why do you simply copy answers from a recent thread at www.teradataforum.com?

The first five phrases were written by you, but the rest is from Dave Wellmann.

And there was another post:

SET/NUPI plus USI still does duplicate row checks for insert/selects,

only single row inserts benefit from the USI.

You can easily see that when you insert/select the same rows a second time:




Re: why should we use SET table ?

Multiset table makes explain plan different from set table when a few table join tgatger and the results return faster. My guess is that Teradat choose a different explan plan when it sees multiset table. But I do not understand why. Have anyone else had a silmilar experince.
Junior Contributor

Re: why should we use SET table ?

When statistics exist on the join columns there should be no different plan and when the plan doesn't change the speed will not change.

But when stats are missing (on the one part of a one-to-many join) the optimizer assumes more than one row will be joined and then overestimates the number of rows returned by the join step. This is a builtin factor, which seems to vary based on some unknown conditions, let's assume 1.1:

Base table has 1.000.000 rows and all the joins are to the PK of the 2nd table:

1st join: estimated 1,100,000, actual 1,000,000

2nd join: estimated 1,210,000, actual 1,000,000

3nd join: estimated 1,331,000, actual 1,000,000


If the factor is different for multiset tables this might explain different plans, but i never tested it.


Re: why should we use SET table ?

Hi friends..!!

i am new for teradata project..

i want to know, what type of error's will come, when data is loading,and how to over come those errors..

and give me some tips, i want to become a best in Teradata Database Development.. please help me..

can any one please explain me..

Thank you.. all of you..!! 

Re: why should we use SET table ?

Hi.. Mr.Dnoeth..

your way of exp is very very good, and easy to understand... i am new for Teradata Project..

please help me..  can you send any dummy project to my mail ID.. Please..

Santhosh.saso@yahoo.in.. this is my mail id..


Re: why should we use SET table ?

In reference to third comment posted by Fred, 

I created a similar table T2_S1 as original source table T1.

CREATE volatile SET TABLE T2_S1 (c1 INTEGER NOT NULL,c2 INTEGER) UNIQUE PRIMARY INDEX (c1) on commit preserve rows;

Now, i tried insert select into this SET table. But, it gave the error.

INSERT T2_S1 SELECT c1,c2 FROM T1;/* succeeds - quietly eliminates the duplicate and inserts 2 rows */

Here it is failing, *** Failure 2801 Duplicate unique prime key error in iTunes_User.T2_MS.

                Statement# 1, Info =0 

 *** Total elapsed time was 1 second.

It seems the insert select for set table case with duplicates being discarded, is not working fine.

Can u please suggest, the behavior here? Is it, when we select all the columns i.e an entire row from the source table, duplicate row check is done for the target table.



Teradata Employee

Re: why should we use SET table ?

Not exactly. Duplicate row check is being done in both cases, but in your example the entire rows (1,1) and (1,2) are not duplicate so neither is eliminated. Therefore the uniqueness violation on the PI is reported.

In my example, the rows (1) and (1) were identical so one was quietly discarded, and there was no uniqueness violation.


Re: why should we use SET table ?

Thanks Fred. I got it know :)


Re: why should we use SET table ?


I am using TD12. I am having three questions regarding the SET tables. 1.Whether a NUPI slow down inserts into the SET tables? 2. Is there any limitation in number of index for set tables loaded by MLOAD? 3. Whether FLOAD performs the duplicate row check in SET tables?

Please advise.

Teradata Employee

Re: why should we use SET table ?

  1. Simply having a NUPI does not slow down inserts, and having a small number of duplicates won't be a problem. But when you insert the Nth row with the same NUPI, Teradata must check against all N-1 existing rows to verify the new row is not a complete duplicate. That overhead grows very quickly as N increases.
  2. No MLOAD-specific limit on number of NUSIs.
  3. FastLoad removes duplicate rows in Phase 2 (even for MULTISET tables). The process is slightly different from SQL INSERT / UPDATE duplicate row check.