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).
Received on Fri Nov 07 2003 - 16:49:34 CST
