Archives of the TeradataForum
Message Posted: Tue, 03 Apr 2001 @ 18:38:34 GMT
| Subj: || || Re: Primary keys in Fact tables for Star Schemas |
| From: || || James Judge |
The choice of the Primary Index (and more importantly the use of the primary index in accessing rows, joins, etc.) is generally, the more
unique the better. Now that is the "general" statement. As in all cases, if there is a predominate access path (i.e certain dimensions are
always in the selection criteria) then you can look at creating the primary index on those columns. The issue with that is getting
acceptable row hash distribution. Teradata handles hash-key synonyms pretty well, i.e. you can have 000's of rows with the same NUPI (hash-
key) value and not see performance issues.
Without knowing access paths (or there is no one predominate access criteria ), then I would start with the single column as the NUPI and
look at secondary index combinations.
Using the 15 columns at the UPI/NUPI is doable but unless the optimizer is able to formulate the entire 15 column hash-key value from a
LT/ST join plan, you will not get any benefit from that primary index.