Archives of the TeradataForum

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

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

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.

Dieter

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