RE: Surrogate keys

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 12 Jun 2001 08:36:03 -0700
Message-ID: <F001.00325AC2.20010612084106@fatcity.com>

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 up doing partitions iteration that leads to scan all partitions in the table when using parallel query ( a bug that I have an open tar for). Even the execution plan says single partition scan or partition iteration based on a range of values, Oracle reads all partitions when using PQ.

Usually I force it to no parallel which much faster.

Regards,

Waleed

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

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
--------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------