RE: 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. If you have numerous concurrent transactions inserting in to the table, then that leaf block becomes a bottleneck (hot block) since all the transactions has to manipulate that leaf block. In high concurrency environments / or in OPS environments, this problem is very cpu intensive. Free lists and free list groups will not help in this case, since all the changes will be done to the single block even if you have multiple free lists/ groups.

        Reverse key indexes are supposed to solve this problem, even though it is not an effective solution. Using some logic to use multiple sequences, one can avoid this problem too.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

Christopher Spence <cspence_at_FuelSpot.com> Sent by: root_at_fatcity.com
06/12/01 08:26 AM
Please respond to ORACLE-L

       
        To:        Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:        
        Subject:        RE: Surrogate keys

Cons
Primary Key has no bearing on data
Primary involves a sequence

Pros
Primary key will never risk change
Primary key will never risk being null
Multiple columns will not migrate to other tables on relationships Very simple to implement
More efficient index use (as you don't have to use 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 ever how ever slim, I generally lean to surrogate keys.

Really good article on Surrogate keys:
http://www.dbpd.com/vault/9805xtra.htm

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

-----Original Message-----
Sent: Tuesday, June 12, 2001 9:00 AM
To: Multiple recipients of list ORACLE-L

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: RICHARD.T.CALE_at_saic.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).