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

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