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:
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.
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..!!
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.firstname.lastname@example.org.. this is my mail id..
In reference to third comment posted by Fred,
INSERT T2_S1 SELECT c1,c2 FROM T1;/* succeeds - quietly eliminates the duplicate and inserts 2 rows */
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.
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?