On Tuesday 12 June 2001 13:19, Jared Still wrote:
The only real downfall of surrogate keys is that it may require a developer
to do a join on the FK to the parent table to get the unique key
information.
Well, there are other downfalls on a system with a high transaction rate
Hi All,
Can someone explain pros/cons to using surrogate keys(i.e.,sequences) vs
non-surrogate keys?
Thanks
Rick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX
Rick,
I use surrogate keys when the obvious primary key value has the possibility
of being updated by the application.
For example, you would think that a Social Security number might be a
candidate for use as a primary key in a college database. What we found
upon study is that, the Soc Sec
Primary key in order of
index build)
Index size is smaller than concatenated primary keys
I generally use Surrogate keys more often than most dbas, I find them great.
But when there is that perfect primary key I will actually opt for a more
intelligent primary key. But when there is any doubt, what so
tables are the database generated primary keys. So when the
application fetches the primary table wit the business key gets the
corresponding surrogate key and navigates through other tables to retrieve
data.
Now another important factor that should be considered while using surrogate
keys
One more cons : If the surrogate keys are populated from a single sequence, then the primary key index of that table becomes right hand index. Meaning all the new values will go in to the right most leaf block of the index due to the nature of the monotonically increasing or decreasing values
Biggest problem in Surrogate keys is partitions elimination does not work.
Usually the selection criteria is defined on the natural keys. So when
joining the dims and fact on the surrogate keys, Oracle can not do a good
job deciding up front which partition it needs. On the other hand it ends
.
IMHO, surrogate keys are always a good idea. Has anyone got an example of
when a surrogate key is NOT a good idea?
The only real downfall of surrogate keys is that it may require a developer
to do a join on the FK to the parent table to get the unique key information.
A small tradeoff in my opinion
