In my past experiences we had a PK created from a source location name
ie: BOST,followed by the julian date,followed by a sequence number with
a max value 9999. The maximun for the pk was 12 characters. Ho would
have more than 10000 entries in a day from one location. The complete
testing of the application with 10001 records proved there could be a pk
problem. We silved the problem by converting the sequence to hex and
then the max was FFFF.
Ron
>>> [EMAIL PROTECTED] 11/10/2003 3:19:42 PM >>>
I created such a beast a few years ago.
The 'customer' gave me no choice. The unique ID for a record
had to be the current date+Id, and the idea had to start over every
day at midnight. That was an interesting bit of code. It
certainly
wouldn't scale, but this was very low volume OLTP, so we could
live with it.
And I certainly didn't use this value as the PK.
Jared
Hemant K Chitale <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/09/2003 07:04 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc:
Subject: Re: Sequences CYCLEing -- was RE: How do you
genrate primary
So, let's start another thread.
How many of you have actually seen Sequences implemented in the manner
I
described
and Mladen demonstrated below ?
Hemant
At 08:24 AM 08-11-03 -0800, you wrote:
>Being sort of DBA Doubting Tom, I have a bad habit of trying and
testing
>stuff. Here is what happens with sequences:
>
>SQL> create sequence test1 start with 1 maxvalue 4 cycle nocache;
>
>Sequence created.
>
>SQL> select test1.nextval from dual
> 2 /
>
> NEXTVAL
>----------
> 1
>
>SQL> /
>
> NEXTVAL
>----------
> 2
>
>SQL> /
>
> NEXTVAL
>----------
> 3
>
>SQL> /
>
> NEXTVAL
>----------
> 4
>
>SQL> /
>
> NEXTVAL
>----------
> 1
>
>SQL> /
>
> NEXTVAL
>----------
> 2
>
>SQL>
>
>On 2003.11.08 10:54, Hemant K Chitale wrote:
>>Ah yes. The exception case when sequence numbers are not unique.
>>Believe me, I've seen Sequences with low MAXVALUE [the guy decided
that
the
>>the number would never exceed 4 digits and didn't want to "waste
resources
>>and space"].
>>And I do vaguely remember that I HAVE seen a Sequence CYCLE over and
>>restart. Can't remember the details, though .... this was many years
ago.
>>It takes all kinds of developers and database designers to make
Oracle
>>interesting.
>>Hemant
>>At 03:29 PM 05-11-03 -0800, you wrote:
>>>In theory I suppose it's possible to have overlaps, but this has
nothing to
>>>do with OPS/RAC. If you create the sequence to CYCLE (not the
default)
AND
>>>set MAXVALUE to something less than reasonable (the default is
NOMAXVALUE
>>>which IIRC means 10 to the power 27) AND don't create a unique index
on
the
>>>column storing the sequence, then maybe you can end up with multiple
rows
>>>having the same value? Never heard of anyone doing that, of course,
but in
>>>theory ...
>>>Pete
>>>"Controlling developers is like herding cats."
>>>Kevin Loney, Oracle DBA Handbook
>>>"Oh no, it's not. It's much harder than that!"
>>>Bruce Pihlamae, long-term Oracle DBA
>>>
>>>-----Original Message-----
>>>Millsap
>>>Sent: Thursday, November 06, 2003 7:34 AM
>>>To: Multiple recipients of list ORACLE-L
>>>
>>>I've never heard of an Oracle sequence not generating unique id's,
OPS/RAC
>>>or not. Gaps, yes. Overlaps, never.
>>>
>>>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-----
>>>Todd Boss
>>>Sent: Wednesday, November 05, 2003 1:09 PM
>>>To: Multiple recipients of list ORACLE-L
>>>There's six very good reasons listed below to NOT use SSN as your
unique
>>>PK,
>>>and honestly I can't believe this is STILL an issue for any dba
>>>who deals w/ SSNs. These arguments are YEARS old. Isn't this Data
>>>Modelling 101? I know for sure this exact case is in every text
i've
read.
>>>How to deal with Natural keys:
>>>- Create a surrogate PK that the user never sees but guarantees
uniqueness.
>>>- Create a separate (unique if you can) index on your "natural
key."
>>>- Go on with life.
>>>I'm a bit more concerned about what i'm hearing about Sequences. Is
it
true
>>>that sequences are NOT guaranteed to be unique?? After all
>>>this time listening to Oracle people scoff at the Sybase/Ms Sql
identity
>>>feature and its inadequacies as compared to Sequences for
generating
>>>sequential surrogate keys .... they're NOT guaranteed to be unique
if
>>>you're
>>>working in a parallel processing environment??
>>>Is this really true? Do Oracle developers have to depend on
>>>circa 1990 techniques to generate something as BASIC as a surrogate
key
by
>>>designing their own little lookup table systems? Or am I just
reading
this
>>>thread incorrectly?
>>>Todd
>>>
>>> >
>>> > I'm fully convinced. SSN should not be used as a PK.
>>> >
>>> > Can we also conclude that natural keys in general are only good
if
you
>>>sit in
>>> > an ivory tower and do unrealistic lab test?
>>> >
>>> > Yong Huang
>>> >
>>> > --- "Bellow, Bambi" <[EMAIL PROTECTED]> wrote:
>>> > > Having worked for the government in a situation where we were
>>>actually
>>> > > tracking information BY Social Security Number, let me tell you
the
>>>problems
>>> > > with it.
>>> > >
>>> > > 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE
>>> > > 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE
Social
>>>Security
>>> > > Number
>>> > > 3) Not all Social Security Numbers are numeric
>>> > > 4) Not all Social Security Numbers which ARE numeric are 9
>>>characters in
>>> > > length
>>> > > 5) Social Security Numbers can be changed by the holder
>>> > > 6) It is illegal to use the Social Security Number for any
purpose
>>>other
>>> > > than that which the government specifically uses Social
Security
>>>Numbers for
>>> > > (ie., the distribution of benefits). I'll bet *that* one is
>>>strictly
>>> > > enforced.
>>> > >
>>> > > HTH,
>>> > > Bambi.
>>> > >
>>> > > -----Original Message-----
>>> > > Sent: Wednesday, November 05, 2003 8:00 AM
>>> > > To: Multiple recipients of list ORACLE-L
>>> > >
>>> > >
>>> > > Tom,
>>> > >
>>> > > I think using a natural key such as Soc. Sec. # as the primary
key
>>>is a good
>>> > > idea. You don't need to maintain the sequence so there's no
>>>performance
>>> > > issue
>>> > > associated with sequences. There's no issue of gaps. No index
root
>>>block
>>> > > contention. It doesn't seem to be industry common practice
though.
>>> > >
>>> > > In your college student case, changing primary keys is rare so
it's
>>>not a
>>> > > big
>>> > > problem.
>>> > >
>>> > > Yong Huang
>>> > >
>>> > > --- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote:
>>> > > > Jonathan,
>>> > > >
>>> > > > I think your idea of a paper is a good one. But I think we
need
>>>to back
>>> > > th
>>> > > > question up to what the requirements are.
>>> > > >
>>> > > > First, to me, a primary key should not be something that a
user
>>>would ever
>>> > > > see or use. So the Soc. Sec. # is out. (A side issue - I
used
to
>>>work at
>>> > > a
>>> > > > college. Want to know how many times we had to change the
Soc.
>>>for an
>>> > > > individual student because the parent filled the form out and
used
>>>their
>>> > > > soc, or the kid used the wrong one?). Any id entered by a
user
is
>>>subject
>>> > > > to mistakes and changes. So the PK value must be protected
from
>>>these
>>> > > types
>>> > > > of errors.
>>> > > >
>>> > > > The next requirement that may be needed is sequentiallity (is
this
>>>a
>>> > > word?).
>>> > > > Does the application require that every sequence number be
used.
>>> > > Sometimes
>>> > > > the answer is yes, and sometimes it just doesn't matter.
>>> > > >
>>> > > > These are the only two requirements I can think of. Based on
the
>>>answers,
>>> > > > we then have options. Right now, Oracle sequences are
working
>>>well for
>>> > > me.
>>> > > > I like the idea of SYS_GUID, just not sure where I would need
it.
>>> > > >
>>> > > > Good idea and good luck!
>>> > > >
>>> > > > Tom Mercadante
>>> > > > Oracle Certified Professional
>>> > > >
>>> > > >
>>> > > > -----Original Message-----
>>> > > > Sent: Wednesday, November 05, 2003 8:19 AM
>>> > > > To: Multiple recipients of list ORACLE-L
>>> > > >
>>> > > >
>>> > > > 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:[EMAIL PROTECTED]
>>> > > >
>>> > > > 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
>>> > > >
>>> > > > 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: [EMAIL PROTECTED] (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: Mercadante, Thomas F
>>> > > > INET: [EMAIL PROTECTED]
>>> >
>>> > __________________________________
>>> > 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: [EMAIL PROTECTED]
>>> >
>>> > 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: [EMAIL PROTECTED] (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: Todd Boss
>>> INET: [EMAIL PROTECTED]
>>>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>>>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: [EMAIL PROTECTED] (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: Pete Sharman
>>> INET: [EMAIL PROTECTED]
>>>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>>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: [EMAIL PROTECTED] (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).
>--
>Mladen Gogala
>Oracle DBA
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mladen Gogala
> INET: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Ron Rogers
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
