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).
Received on Wed Nov 05 2003 - 13:14:28 CST
