RE: RE: How do you generate primary keys?

It was a write ahead error :(

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On
> Behalf Of Melanie Caffrey
> Sent: 07 November 2003 22:50
> To: Multiple recipients of list ORACLE-L
> Subject: RE: RE: How do you generate primary keys?
>
>
> Ahh ... looks like you missed Henry Poras's reply,
> Niall.
>
> He replied with the same answer for this one. ;)
>
>
> --- Niall Litchfield <niall.litchfield_at_dial.pipex.com>
> wrote:
> > 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_at_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).
>
>
> __________________________________
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard
> http://antispam.yahoo.com/whatsnewfree
> --
> Please see the
> official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Melanie Caffrey
> INET: melanie_caffrey_at_yahoo.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).