Surrogate keys are very common in DW environment for some of the benefits it provides.
Vertica recommends to use Surrogate keys or Natural keys?
Usage of surrogate keys in Vertica
+1
+2The same considerations in choosing a surrogate versus a natural key apply. The only suggestion I have is to avoid GUID externally generated as they do not compress very well. A sequencer would work well/.
I
+1Hi!
You need surrogate key only if you have no simple Natural key (when natural key is a several fields, or field is complex like GUID as Colin pointed). I don't see any reason for surrogate key, if you have a simple natural key.
From my experience - there are no such big difference in Vertica, because it is columnar DBMS and columns are sorted. Once, at AT&T I had a table with140 columns where 10 of them where a PK and I wanted yo improve a JOIN. I defined a surrogate key as hash of 10 fields and didn't get any performance boost. Actually i got some boost, but less than 2%, its not critical (Vertica version 6.1.2).
Of cause it depends on schema and requirements, here no silver bullets.
PS
Surrogate keys simplifies query writing/development. Its important too - less place for mistakes.
You need surrogate key only if you have no simple Natural key (when natural key is a several fields, or field is complex like GUID as Colin pointed). I don't see any reason for surrogate key, if you have a simple natural key.
From my experience - there are no such big difference in Vertica, because it is columnar DBMS and columns are sorted. Once, at AT&T I had a table with140 columns where 10 of them where a PK and I wanted yo improve a JOIN. I defined a surrogate key as hash of 10 fields and didn't get any performance boost. Actually i got some boost, but less than 2%, its not critical (Vertica version 6.1.2).
Of cause it depends on schema and requirements, here no silver bullets.
PS
Surrogate keys simplifies query writing/development. Its important too - less place for mistakes.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
