RE: Surrogate keys

From: Murali Vallath <murali_vallath_at_hotmail.com>
Date: Tue, 12 Jun 2001 07:36:03 -0700
Message-ID: <F001.003255F9.20010612063619@fatcity.com>

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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: 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: Mercadante, Thomas F

INET: NDATFM_at_labor.state.ny.us

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

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: murali_vallath_at_hotmail.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