Re: How do you genrate primary keys?

sandali- may tatapusin lang ako. I'll call you in 30 minutes.

  wrote:

>You're much too nice.
>
>
>
>
>
> Rachel
>
> Carmichael To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> <wisernet100 cc:
>
> @yahoo.com> Subject: Re: How do you
> genrate primary keys?
> Sent
> by:
>
> ml-errors
>
>
>
>
>
> 11/05/2003
> 09:44
>
> AM
>
> Please
> respond
>
> to
> ORACLE-L
>
>
>
>
>
>
>
>
>
>It was a compromise... since they had already written their code, I put
>in the triggers so that it was transparent to them that the "key" they
>were generating was not being used.
>
>I had to give them something, since I was really trying hard NOT to say
>"I told you so!"
>
>
>--- Yong Huang <yong321_at_yahoo.com> wrote:
> > Rachel,
> >
> > That's a good case to remember. Java programmers (or architects)
> > sometimes miss
> > those little things.
> >
> > I would ask why you used triggers to populate the PK field instead of
> > saying
> > INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... SELECT
> > ROWNUM
> > (or ROWNUM+somefixedvalue). Wouldn't these perform better?
> >
> > Yong Huang
> >
> > --- Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> > > At one site I worked at, the programmers insisted on using Java
> > > milliseconds as the primary key -- so that they wouldn't have to
> > hit
> > > the database twice (once to get the sequence number, once to insert
> > the
> > > row). They swore up, down and six ways from Sunday that there could
> > > never, ever, EVER be a collision.
> > >
> > > After we had collisions in development, we switched to sequences
> > (one
> > > per table), with a trigger to populate the field on insert so that
> > they
> > > wouldn't have to make the second round-trip.
> > >
> > >
> > > --- Jonathan Gennick <jonathan_at_gennick.com> 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).
> > >
> > >
> > > __________________________________
> > > 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: Rachel Carmichael
> > > INET: wisernet100_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).
> >
> >
> > __________________________________
> > 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).
>
>
>__________________________________
>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: Rachel Carmichael
> INET: wisernet100_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: Thomas Day
> INET: tday6_at_csc.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: Jerome Roa
  INET: jroa_at_uchicago.edu

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).