True and not true,
If the database is partitioned and using a local index. then you may want to
use the business key as a prefixed index. This way you could avoid the
additional index on the table.
NOTE: The table is partitioned by the surrogate key and the all foreign keys
in the child 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 is using REVERSE key indexes. When you have high volume inserts, the
leaf block contention of using sequence generated keys is quite extensive.
Regards,
Murali Vallath
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 12 Jun 2001 05:45:23 -0800
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 # provided at college application time is
sometimes the parents number, rather than the student's number. So, it
changes about 10% of the time.
Of course, the cons of primary keys are that you probably will need an
additional index on the table to allow lookup by a secondary value. In the
above case, the primary key was a surrogate value supported by a sequence,
with a secondary index on the soc-sec column. The cost of using the
surrogate primary key was the additional index.
My current database that I am designing is only using surrogate keys. It
provides a level of comfort in that we don't ever have to worry about the
primary key being changed - it never will.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-----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: Mercadante, Thomas F
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).
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Murali Vallath
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).
