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" <bbellow_at_chi.navtech.com> 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" <NDATFM_at_labor.state.ny.us> 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: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).
>> > > > --
>> > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
>> > > > --
>> > > > Author: Mercadante, Thomas F
>> > > > INET: NDATFM_at_labor.state.ny.us
>> >
>> > __________________________________
>> > 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: Todd Boss
>> INET: boss_at_i-sphere.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: Pete Sharman
>> INET: peter.sharman_at_oracle.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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mgogala_at_adelphia.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).
Received on Sat Nov 08 2003 - 10:24:24 CST
