RE: RE: How do you generate primary keys?

Rollback?

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> Behalf Of Cary Millsap
> Sent: 05 November 2003 20:39
> To: Multiple recipients of list ORACLE-L
> Subject: RE: RE: How do you generate primary keys?
>
>
> I've heard of people using instance startup triggers to
> insert VOID records in cases where there are gaps. I haven't
> thought about it much recently, but I can't presently think
> of occasions when gaps occur other than instance shutdown.
>
>
> 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-----
> Jamadagni, Rajendra
> Sent: Wednesday, November 05, 2003 12:14 PM
> To: Multiple recipients of list ORACLE-L
>
> 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).
>
> --
> 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: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.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).