Archives of the TeradataForum

Message Posted: Fri, 05 Jan 2007 @ 15:31:58 GMT


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

Subj:		Re: Creating tables 50% to 90% of columns as unique primary index

From:		Bhanu Govindu

You can enforce Uniqueness of a record (all columns) at the table level, Using a SET table, but to avoid duplicates-on-Key you may use UPI, USI/Unique Constraint at the table level. USI in your case, comes with a diskspace cost.

>From loading perspective, the slowest from my experience is a table Structure with (SET,NUPI), Next slowest is (MULTISET/SET,UPI) and the fastest among the 3 being (MULTISET,NUPI).

When the above structures are empty, they may not show huge time or cpusec Differences when data is loaded into them but, tend to show slower loading times once The tables have a bunch of million records (say about 5-10 mill).

If you can guarantee uniqueness from ETL/Source then you may opt for multiset,NUPI Else you may have to go with other existing options.

>From User Querying perspective, you have to see what columns are used frequently To join/filter on this table. If the frequently joined columns with other tables are a subset or a-set of unique columns then , you may consider making that a PI provided it does not skew the table beyond your tolerable limit and provides a considerable Performance benefit.


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