Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: How do you generate primary keys?

RE: RE: How do you generate primary keys?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 05 Nov 2003 10:14:26 -0800
Message-ID: <F001.005D5ADC.20031105101426@fatcity.com>


Ryan,

hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, would you still use sequences?

Raj



Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Wednesday, November 05, 2003 12:09 PM To: Multiple recipients of list ORACLE-L

do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them.

id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well?
>
> From: "Cary Millsap" <cary.millsap_at_hotsos.com>
> Date: 2003/11/05 Wed AM 11:04:25 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: How do you genrate primary keys?
>
> "Hit a table that keeps a counter" will not scale (will not perform at
> high concurrency). It will cause you no end of "buffer busy waits"
> waits, "latch free" waits for a cache buffers chains latch (even if
> db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches
> could be set to infinity), lots of unnecessary CPU service consumption
> due to the spinning (especially if you try to tinker with _spin_count),
> and possibly a wide range of side effects including "write complete
> waits" waits and others.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Performance Diagnosis 101: 11/19 Sydney
> - SQL Optimization 101: 12/8-12 Dallas
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> Hemant K Chitale
> Sent: Wednesday, November 05, 2003 8:25 AM
> To: Multiple recipients of list ORACLE-L
>
>
> My comments [probably off-the-cuff without spending much time
> thinking the issues through .....?]
>
> 1. Hit a table that keeps a counter.
> Used to be a mechanism in the Oracle5 days [If I remember correctly,
> Sequences came in Oracle6]. Issues were with locking the single
> record used as the generator or scanning for the max(value) of the
> key.
> Not quite sure I understand how you encountered concurrency issues,
> though.
>
>
> 2. Stored sequences.
> Although I prefer not to use a Sequence as a PK in itself [preferring
> natural column/s which are Unique keys, with the NOT NULL, of course],
> I have used a Sequence in an Advanced Replication implementation that
> had no Primary Key and I needed a PK for Conflict Resolution [this was
> years
> ago and, if you ask me, I can't remember all the details]
>
> 3. SYS_GUID
> SYS_GUID I've never used. It doesn't generate a NUMBER value
> so it is not really similar to a Sequence.
> Can user's key in a SYS_GUID-generated value ? Is it really
> "human readable" or "recallable" as a plain NUMBER, Security Security
> Number,
> ZIP Code ??
>
> 4. Similar to SYS_GUID ..
> You hit on a fortuitous combination of columns.
>
>
> Hemant
>
> At 05:19 AM 05-11-03 -0800, you wrote:
> >The recent article that mentioned sequences got me to
> >thinking. I might pitch a more detailed article on sequences
> >to Builder.com. But a more interesting article might be one
> >that explored various ways to automatically generate primary
> >keys. So, in the name of research, let me throw out the
> >following questions:
> >
> >What mechanisms have you used to generate primary keys?
> >Which ones worked well, and why? Which mechanisms worked
> >poorly?
> >
> >I've run up against the following approaches:
> >
> >* Hit a table that keeps a counter. This is the "roll your
> >own sequence method". The one time I recall encountering
> >this approach, I helped convert it over to using stored
> >sequences. This was because of concurrency problems: with
> >careful timing, two users could end up with the same ID
> >number for different records. Is there ever a case when this
> >roll-your-own approach makes sense, and is workable?
> >
> >* Stored sequences. I worked on one app that used a separate
> >sequence for each automatically generated primary key. I
> >worked on another app, a smaller one, that used the same
> >sequence for more than one table. The only issue that I
> >recall is that sometimes numbers would be skipped. But end
> >users really didn't care, or even notice.
> >
> >* The SYS_GUID approach. I've never used SYS_GUID as a
> >primary key generator. I wonder, was that Oracle's
> >motivation for creating the function? Has anyone used it for
> >primary keys in a production app? What's the real reason
> >Oracle created this function?
> >
> >* Similar to SYS_GUID, I once worked on an obituary-tracking
> >application that built up a primary key from, as best I can
> >recall now: date of death, part of surname, part of first
> >name, and a sequence number used only to resolve collisions,
> >of which there were few. The approached worked well,
> >actually, because whatever fields we munged together to
> >generate a primary key gave us a unique key the vast
> >majority of the time.
> >
> >The SYS_GUID approach is interesting, but if you need an ID
> >number that users will see, and that users might type in
> >themselves (e.g. social security number), is SYS_GUID really
> >all that viable?
> >
> >Best regards,
> >
> >Jonathan Gennick --- Brighten the corner where you are
> >http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
> >
> >Join the Oracle-article list and receive one
> >article on Oracle technologies per month by
> >email. To join, visit
> >http://four.pairlist.net/mailman/listinfo/oracle-article,
> >or send email to Oracle-article-request_at_gennick.com and
> >include the word "subscribe" in either the subject or body.
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >--
> >Author: Jonathan Gennick
> > INET: jonathan_at_gennick.com
> >
> >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >San Diego, California -- Mailing list and web hosting services
> >---------------------------------------------------------------------
> >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).
>
> Hemant K Chitale
> Oracle 9i Database Administrator Certified Professional
> My personal web site is : http://hkchital.tripod.com
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Hemant K Chitale
> INET: hkchital_at_singnet.com.sg
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: <ryan_oracle_at_cox.net
  INET: ryan_oracle_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

**************************************************************************************
This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.
**************************************************************************************4
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
Received on Wed Nov 05 2003 - 12:14:26 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US