Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 12 Feb 2002 @ 08:54:21 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Insert data into table
From:   Dieter Nöth

  I read any articles int this list about INSERT and I understood that is better use MULTISET than SET. I understood that SET not allow duplicate rows and MULTISET allow it. My question is if I've a UNIQUE PRIMARY INDEX on a table and I created it using MULTISET, this table not allow duplicate rows, because the UNIQUE INDEX doesn't allow it. That is TRUE?  

Only MULTISET _and_ NUPI make sense.

If you query the system tables for multiset tables you'll see that MULTISET + UPI is treatet as SET. You have to create your own view because it's not in the system views, the checkopt column in dbc.tvm is used for indicating multiset, if it's 'y' and tablekind = 't' then it's multiset.

If you want to test the overhead of duplicate row checks:

create set table settest(i int; data char(200));
insert into settest select 1 from sys_calendar.calendar;

Have a long break, watch CPU Usage of 1 CPU at 100%: all records have the same PI, so they're all stored on a single AMP, an AMP uses only 1 CPU and it's doing n * (n-1) / 2 duplicate row checks (~73000 records, so > 2.500.000.000 checks).

Note: a duplicate row check is not only comparing byte by byte, if there's a case insensitive char column.

Don't do this on your production nodes ;-)

Probably cancel the query and try the same with multiset.

Conclusion: There will be a massive performance boost during load especially for tables with a large number of rows/value (you'll see a difference even for 10 rows/value or less).

Consider MULTISET even for tables with USI when you use Multioad: you have to drop the USI before load and recreate it later. You'll get a problem when you load a duplicate row and try to recreate the USI, but it's the same problem, if you load a duplicate USI value.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020