Archives of the TeradataForum

Message Posted: Thu, 11 May 2006 @ 10:06:24 GMT

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

The concept of SET / MULTISET seems to be specific to Teradata. It is quite a simple concept (its implementation is another issue, of course) but for some reason it seems to be causing confusion. Commenting on it is not easy as each new comment just seems to add to the confusion but I will have a quick go on it anyway.

Try to first think of SET / MULTISET as a very generic table feature which is on a higher level than primary index. OK, so you are thinking of defining a new table. If you want to make sure that there will be no duplicates in it (by "duplicates" here I mean rows which have exactly the same values in *all* the columns; if there is at least one column with different values then the rows are not duplicate), you might opt for the SET table. This is a Teradata specific feature (in fact, a luxury option) which will guarantee that under no circumstances the table will contain duplicates. Of course, this safety won't come for free, there will be performance penalty as each DML statement on the table will have to be checked for potential duplicates.

At this point you need to decide whether you want to use this luxury SET feature or not. Such databases as Oracle do not have a similar feature but they still manage to live without duplicates if they take care about them. For example, they use primary keys or unique constraints to prevent duplicates. Or they use reliable ETL software which guarantees uniqueness outside the database.

In Teradata, you can use the same alternatives to SET if you are sure that youcan avoid duplicates and beat SET performance. You then go for MULTISET and use one of the options above.

UPI is just one of the alternatives. The simplest case perhaps. If you can afford defining a UPI on your table (and the number of keys in it is less then the total number of columns in the table) then you already guarantee uniqueness of rows on all columns (but of course). Please note that I put a number of IFs here, and don't interpret this statement as a general advice to abandon SET tables and go for UPI (I for one never give general advice; I like special cases but that is aside). My suggestion would merely be to take a look at alternatives to SET before you decide how to define your table. SET is not a "default" option; there are alternatives to it but they come with extra responsibilities.

Regards,

Victor

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