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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/12/01 08:26 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Khedr, Waleed
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
