Re: How do you genrate primary keys?

Hi:

    Selecting from a table to generate PK is not good, not mainly because of performance, but because of scalability.     To generate unique PK, you have to do select max(pk_column) from tab for update , if this session does not commit, others cannot select. Without using for update, though other people can, this caused duplicate records.     I have seen many customer using this kind of method to generate PK for that so called and useless contiueous PK.     

Zhu Chao.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, November 06, 2003 12:24 AM

> Cary,
>
> If hitting a table that keeps a counter causes so many performance problems, I
> wonder why hitting sys.seq$ is much faster. I'd like to have some education on
> this Oracle magic. The only thing I can think of is that Oracle keeps some
> numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do
> that.
>
> Yong Huang
>
> --- Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> > "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).
>
>
> __________________________________
> 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: Yong Huang
> INET: yong321_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: zhu chao
  INET: chao_ping_at_vip.163.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).