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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[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
RE: How do you genrate primary keys?
Oracle has RETURNING clause for insert. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Stefan Jahnke Sent: Monday, November 10, 2003 10:34 AM To: Multiple recipients of list ORACLE-L Hi Rachel In most Java applications I've seen so far, the issue of caching rows by an id, which is usually the primary key, arises. JDBC v3 implements a method which allows you to return a key after the insert completed (for example MS SQL Server can do this). How do you get a hold of the PK, after you inserted the key via trigger without an extra roundtrip ? Regards, Stefan -Ursprüngliche Nachricht- Von: Rachel Carmichael [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 5. November 2003 14:34 An: Multiple recipients of list ORACLE-L Betreff: Re: How do you genrate primary keys? 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 [EMAIL PROTECTED] 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:[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). __ 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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: How do you genrate primary keys?
As I understand, in case of cached sequences, SEQ$ is touched only when you run out of cached values in library cache and a new sequence range has to be allocated. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:39 PM That's it. If you didn't use the cache, then it would cause the same problem as with normal table-managed sequence numbers. But with cached sequence numbers, an application can get a sequence number without touching the database (SEQ$) at all. 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... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
Sequences CYCLEing -- was RE: How do you genrate primary keys?
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
Re: Sequences CYCLEing -- was RE: How do you genrate primary keys?
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,
Re: How do you genrate primary keys?
Exactly. Get a design with three or four levels of PK/FK relationships and watch the cascading everytime a mistyped natural PK needs to be edited to the correct value. It's just not practical. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - eg. if you update the natural key in the parent, you must also update the natural key everywhere it appears as an fk. This gets really ugly when you think about making modifications to the schema. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: How do you genrate primary keys?
Jonathan
Yet another way [I do understand the drawbacks :)]:
* Table with the sequences my_sequences -- for preliminary definition
sys.seq$ can be considered, if possible each row is placed into dedicated
block (number of sequence does matter in this case so, it's a search for
trade off) -- could help when there is a lot of concurrent requesters.
Suppose number of such sequences defined as N.
* FUNCTION get_next_range('sequence', range) (AUTONOMOUS transaction)
returns next number and updates the sequence number according to
the requested range -- so a caller would get the small pool (session
sub-pool) of sequences. 'SQ' can be emulated via dbms_lock, if needed,
that would take additional time, of course.
* FUNCTION get_next('sequence') that would return next value from the
pool of requested numbers -- it would not touch my_sequences unless
it's out of numbers.
* FUNCTION get_current('sequence'), emulates .CURRVAL
For example, the caller knows that it would need 10 unique numbers for
the given transaction:
1. get_next_range('COMMON_SEQUENCE', 10); - 17
(if someone else call get_next_range now he would get 27)
2. INSERT INTO table_a (id, name) VALUES(get_next('COMMON_SEQUENCE',
'test'); etc.
Practically it does emulate regular sequences but it allows each
caller to define its own pool, that could be a bit more efficient
but requires some programming and accuracy.
Also, one can consider something like:
CHR(65 + MOD(SYS_CONTEXT('USERENV', 'SESSIONID'), N))
add this prefix and make primary keys e.g. character based (or play
around and stick with numeric values only) using the approach described
above. It would make particular sessions based on particular sequence
entry of my_sequences table.
Gaps are unavoidable here.
*Drawbacks* are *clear*, so it's just an idea that can be
implemented and tested. I'm pretty sure that the approach
is disputable, so please let's avoid long discussions.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Jonathan Gennick 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,
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
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).
Re: How do you genrate primary keys?
I'm a bit surprised no one's mentioned it, but there's an article about the use of surrogate keys at Ixora : http://www.ixora.com.au/tips/design/synthetic_keys.htm mvg/regards Jo Jonathan Gennick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 14:19 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How do you genrate primary keys? 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: 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).
RE: How do you genrate primary keys?
Bambi - Actually, the government is moving to enforcement of the use of SSAN for miscellaneous purposes. We've had to quit sending SSAN to health insurance companies. I believe this is the new HIPPA law. Just another reason to not use SSAN as a primary key. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 11:35 AM To: Multiple recipients of list ORACLE-L 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
RE: How do you genrate primary keys?
What I mean't was, and maybe this is different in different countries, if we have an employee who leaves us, realizes just what a nice guy I was to work with :(, and returns then one of two things will happen. If they return in a different tax year, they get the same empno, but for a different employment. If they return in the same tax year they get a different empno, for tax accounting reasons. Thus the same employee can reuse a key for a different logical employment, or have a different key for a different employment. The business rules mean that they are bad keys Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TOMPKINS, MARGARET Sent: 05 November 2003 21:25 To: Multiple recipients of list ORACLE-L Subject: RE: How do you genrate primary keys? The thought is that if it is internal then you control it. Of course, it doesn't mean you will do it right. ;-) Maggie -Original Message- Sent: Wednesday, November 05, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Except of course that internal employee ids also can get reused, and the converse the same individual can have more than one employee id. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TOMPKINS, MARGARET Sent: 05 November 2003 14:10 To: Multiple recipients of list ORACLE-L Subject: RE: How do you genrate primary keys? Social security numbers are notoriously bad natural primary keys. Did you know that they are re-used? Yes, it's true. Generally, they don't get re-issued until after one of the users dies, but it's been a problem in the past and still is. What do you do with people who don't have SSNs? Foreign nationals and others that work for US companies oversees or provide goods/services generally do NOT have SSNs. An internal employee id would be a much better choice if a natural primary key is needed. Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -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
RE: How do you genrate primary keys?
This discusson seems to converge with the update-forigin-key topic. load - shipment - truck vs employee - employment - company. DBA!ert, Independent Oracle Consultancy Kastanjelaan 61C 2743 BX Waddinxveen The Netherlands tel.+31 (0) 182 640 428 fax +31 (0) 182 640 429 mobile +31 (0) 653 911 950 e-mail [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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).
Re: How do you genrate primary keys?
One thing missed in that article is that the use of natural keys as foreign keys does not follow the relational model closely, and presents an update anomoly. eg. if you update the natural key in the parent, you must also update the natural key everywhere it appears as an fk. This gets really ugly when you think about making modifications to the schema. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 12:19 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: How do you genrate primary keys? I'm a bit surprised no one's mentioned it, but there's an article about the use of surrogate keys at Ixora : http://www.ixora.com.au/tips/design/synthetic_keys.htm mvg/regards Jo Jonathan Gennick [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 14:19 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:How do you genrate primary keys? 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: 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).
Re: How do you genrate primary keys?
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 [EMAIL PROTECTED] 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:[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). __ 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: [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).
RE: How do you genrate primary keys?
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] 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
RE: How do you genrate primary keys?
don't you hate this arguement? Of course, your solution solves the problem - use triggers to populate the column. it shows that the developers just don't understand all of the tools that they have available to them. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:34 AM To: Multiple recipients of list ORACLE-L 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 [EMAIL PROTECTED] 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:[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). __ 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: [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:
RE: How do you genrate primary keys?
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:
RE: How do you genrate primary keys?
Social security numbers are notoriously bad natural primary keys. Did you know that they are re-used? Yes, it's true. Generally, they don't get re-issued until after one of the users dies, but it's been a problem in the past and still is. What do you do with people who don't have SSNs? Foreign nationals and others that work for US companies oversees or provide goods/services generally do NOT have SSNs. An internal employee id would be a much better choice if a natural primary key is needed. Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -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
RE: How do you genrate primary keys?
Mercadante, Thomas F scribbled on the wall in glitter crayon: 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 other problem is the SS# are not unique. so even if they get entered correctly you can still have a dupe. trust me in this one, i had to pay a lawyer to get it straightened out. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] This planet has - or rather had - a problem, which was this: most of the people living on it were unhappy for pretty much of the time. Many solutions were suggested for this problem, but most of these were largely concerned with the movements of small green pieces of paper, which is odd because on the whole it wasn't the small green pieces of paper that were unhappy. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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).
Re: How do you genrate primary keys?
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[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). __ 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: [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
Re: How do you genrate primary keys?
The only other method that I've seen that hasn't been mentioned is to generate the primary key of a new row as max(primary_key)+1. Inefficient as all get out but I've seen it done on small tables with very low volatility where the business rules required absolute sequentiality. It worked but I'd only recommend it under very specific circumstances. Jonathan Gennick jonathanTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @gennick.comcc: Sent by: Subject: How do you genrate primary keys? ml-errors 11/05/2003 08:19 AM Please respond to 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
Re: RE: How do you genrate primary keys?
i dont think social security number is actually unique. I heard that there are some repeats and there are problems with people who are 80 years old drawing money out of accoutns of people who are 25 due to this problem. i know its a standard to use SSN as a key, but it might not be accurate. From: Yong Huang [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 08:59:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? 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
RE: How do you genrate primary keys?
Yong, I hope you read the other replies. Soc. is the *worst* use of a PK if there ever was one. You say it is a minor problem so it can be easily changed. What if the SOC is used as the PK/FK in a hundred tables in your system? Is this an easy change? The first rule of thumb about PK's is - never change it Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 9: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
Re: How do you genrate primary keys?
My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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:[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). 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
RE: How do you genrate primary keys?
I think using a natural key such as Soc. Sec. # as the primary key is a good idea. it is VERY VERY BAD idea. Oh yea. This is the first thought that these numbers are unique. But the real life is completely different. Especially where data about people are involved. So... We had such a problem in Latvia with population register. Every people __should__ have a unique Person identifying number that was constructed from the birth day, month and year and sequence number. Blahhh!! As it was more than 10 years ago we haven't online databases in every registration point and as a result we got people with duplicated PiNos. This problem was actual starting from year ~1992 and ending in ~1998 when I left this state agency. I'm not sure about the situation today maybe some app that uses his own PKs is created. So I'm very strong supporter of surrogate PKs and I use them always in all projects that I've managed or participated as an analyst/architect. And I'v accepted designer standards about three leter abbreviations for tables and use them for sequences for these tables (eg. adr_seq), all column names (eg. adr_city), constraints (eg. adr_pk, adr_uk1, adr_ck1, adr_zon_fk). Gints 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 -Original Message- From: Yong Huang [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 4:00 PM To: Multiple recipients of list ORACLE-L Subject: RE: How do you genrate primary keys? 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
Re: How do you genrate primary keys?
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[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). __ 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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
Re: How do you genrate primary keys?
Of course, another reason to use a trigger is so that PKs are correctly generated _regardless_ of the application that's doing the inserting. --- Rachel Carmichael [EMAIL PROTECTED] wrote: 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[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).
RE: How do you genrate primary keys?
Title: RE: How do you genrate primary keys? SSAN are not reused by the government at least on purpose. Check it out below: http://tinylink.com/?WCzYP7kRi2 However there are many other problems with SSANs. - Sometimes they are accidentally duplicated. Stuff happens when you issue 6 million a year. - They are often fraudulently used. I did some work for the fraud and bad check department of a bank and saw a lot of it. - As you said, only the good old USA uses them. What do you do when your company starts tracking employees overseas? - The SSANs start with a leading zero(s) in the northeast. I've seen people store them as a number (they are call Social Security NUMBERS after all) and then wonder why the zeros are missing. I agree they are bad primary keys. Of course I think any natural key is a bad primary key. ;-) In fact, you might even change your mind about Employee IDs once you merged systems where one company has been bought out by another. I've seen that get ugly. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: TOMPKINS, MARGARET [SMTP:[EMAIL PROTECTED] Social security numbers are notoriously bad natural primary keys. Did you know that they are re-used? Yes, it's true. Generally, they don't get re-issued until after one of the users dies, but it's been a problem in the past and still is. What do you do with people who don't have SSNs? Foreign nationals and others that work for US companies oversees or provide goods/services generally do NOT have SSNs. An internal employee id would be a much better choice if a natural primary key is needed. Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED]
RE: How do you genrate primary keys?
Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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:[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
RE: RE: How do you genrate primary keys?
Yep, in the USA, SSN is very not unique. 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- [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L i dont think social security number is actually unique. I heard that there are some repeats and there are problems with people who are 80 years old drawing money out of accoutns of people who are 25 due to this problem. i know its a standard to use SSN as a key, but it might not be accurate. From: Yong Huang [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 08:59:34 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? 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
RE: How do you genrate primary keys?
Cary, If hitting a table that keeps a counter causes so many performance problems, I wonder why hitting sys.seq$ is much faster. I'd like to have some education on this Oracle magic. The only thing I can think of is that Oracle keeps some numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do that. Yong Huang --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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:[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
Re: RE: How do you genrate primary keys?
do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle
Re: How do you genrate primary keys?
i think thats how mysql does it. joe Thomas Day wrote: The only other method that I've seen that hasn't been mentioned is to generate the primary key of a new row as max(primary_key)+1. Inefficient as all get out but I've seen it done on small tables with very low volatility where the business rules required absolute sequentiality. It worked but I'd only recommend it under very specific circumstances. Jonathan Gennick jonathanTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @gennick.comcc: Sent by: Subject: How do you genrate primary keys? ml-errors 11/05/2003 08:19 AM Please respond to 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--
RE: How do you genrate primary keys?
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
RE: How do you genrate primary keys?
That's it. If you didn't use the cache, then it would cause the same problem as with normal table-managed sequence numbers. But with cached sequence numbers, an application can get a sequence number without touching the database (SEQ$) at all. 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- Yong Huang Sent: Wednesday, November 05, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Cary, If hitting a table that keeps a counter causes so many performance problems, I wonder why hitting sys.seq$ is much faster. I'd like to have some education on this Oracle magic. The only thing I can think of is that Oracle keeps some numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do that. Yong Huang --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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
RE: How do you genrate primary keys?
and it's only slightly better if you have more than one row in that table. As in, the app the developers here use to generate code keeps a table of tablenames and their associated last number used why they felt the need to reinvent the wheel I don't know. For this app, I couldn't use natural keys as some of them would involved multiple columns or alphanumeric characters and the app generator couldn't handle it. --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit
Re: How do you genrate primary keys?
Yong, sorry but they are federal law prohibiting using SSN as a key, so the point is moot. joe Yong Huang wrote: 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
RE: RE: How do you genrate primary keys?
Occasionally I see this. It's always a mistake. I probably see a higher percentage of people that have this problem than most, because, by the design of my job, practically the *only* systems I see are ones that have performance problems. Using a table as a counter is almost guaranteed to cause problems unless you have only a single-user system. 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- [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 11:09 AM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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
RE: RE: How do you genrate primary keys?
There are also rare cases where primary keys are mandated to be consecutive numbers such that a select of nextval, if it were not used, would invalidate the key. Rare, but out there. Bambi. -Original Message- Sent: Wednesday, November 05, 2003 11:09 AM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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
RE: How do you genrate primary keys?
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
Re: How do you genrate primary keys?
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?
RE: How do you genrate primary keys?
No, you cannot. Most entities have natural primary keys. People are the exception not the rule. I am not advocating the use of natural keys as the primary keys of tables. I like to sue sequnece numbers for that purpose. However the natural key should be identified and enforced via a unique constraint. If you only have a sequenced-based primary key how do you protect against duplicate entries? We have that problem with our personnel data because all it has is such a key, and our physics collaborations are world-wide. Different transliterations, switching of first and last names, and individuals without surnames can make life interesting. We have one person who only has a surname. I would think think that must be confusing at home. Perhaps they use a system similar to that in the old joke about the folks in Welsh village: Jones, the baker; and Jones, the post; and Jones the We have a program which helps with these problems, but it does not totally prevent someone from being in the database twice for a short time. I'd hate to think what are database would be link if we didn't enforce natural keys on our other tables. Ian MacGregor Stanford Linear Accelerator Cenr -Original Message- Sent: Wednesday, November 05, 2003 10:34 AM To: Multiple recipients of list ORACLE-L 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
Re: How do you genrate primary keys?
You're much too nice. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
RE: How do you genrate primary keys?
For entity uniqueness you have a unique identifier. You might even have more than one. For drawing entity relationship diagrams however, I don't know of any tool that allows you to display more than one, so you have a primary unique identifier and perhaps other unique identifiers that exist but don't show up on an ERD. When the entity gets transformed into a table, each of the unique identifiers should get implemented with a unique key constraint. That is the natural unique identifier on the entity becomes a unique key on the table. The table also gets the sequence generated surrogate primary key that we have been talking about. For the names you describe, some people only require one name like Cher and Madonna. If the unique key is made up of several components like first name, last name, etc. then you could have NULL for a last name to accommodate Cher and her friends. That works nicely in a unique key but of course, you can't have NULL as a component of a primary key. However, only one Cher would be allowed in the table. Maggie Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 1:15 PM To: Multiple recipients of list ORACLE-L No, you cannot. Most entities have natural primary keys. People are the exception not the rule. I am not advocating the use of natural keys as the primary keys of tables. I like to sue sequnece numbers for that purpose. However the natural key should be identified and enforced via a unique constraint. If you only have a sequenced-based primary key how do you protect against duplicate entries? We have that problem with our personnel data because all it has is such a key, and our physics collaborations are world-wide. Different transliterations, switching of first and last names, and individuals without surnames can make life interesting. We have one person who only has a surname. I would think think that must be confusing at home. Perhaps they use a system similar to that in the old joke about the folks in Welsh village: Jones, the baker; and Jones, the post; and Jones the We have a program which helps with these problems, but it does not totally prevent someone from being in the database twice for a short time. I'd hate to think what are database would be link if we didn't enforce natural keys on our other tables. Ian MacGregor Stanford Linear Accelerator Cenr -Original Message- Sent: Wednesday, November 05, 2003 10:34 AM To: Multiple recipients of list ORACLE-L 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
Re: How do you genrate primary keys?
I save the beatings for when they truly deserved it. Besides, it was enough to have them come to me in remorse telling me I was right. From then on, they never had a design meeting without me there. --- Thomas Day [EMAIL PROTECTED] wrote: You're much too nice. Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
RE: RE: How do you genrate primary keys?
Ryan, Never used PeopleSoft, huh? Henry -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 12:09 PM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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
RE: How do you genrate primary keys?
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
RE: RE: How do you genrate primary keys?
WAR STORY In an application a former company developed, the primary developer decided to use a table because he'd heard that sequences were unreliable. So we had this table...1 row ...lots of columns of sequence keys. Anytime we needed to add a key it was a major problem because you couldn't just add a column in those days, and they wanted them in alphabetical order...and it kept becoming locked by a user who went to lunch before committing. Sigh. They never understood why I thought this was poor design. /WAR STORY -Original Message- Sent: Wednesday, November 05, 2003 12:24 PM To: Multiple recipients of list ORACLE-L Ryan, Never used PeopleSoft, huh? Henry -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 12:09 PM To: Multiple recipients of list ORACLE-L do people actually use a table as a counter these days? Now Im 'assuming' they are jsut people who dont know about sequences or are there actually 'professionals' who know about sequencse and decide not to use them. id assume those tables were used in oracle 5 days because either sequences didnt exist or they werent designed well? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/05 Wed AM 11:04:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: How do you genrate primary keys? Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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
Re: How do you genrate primary keys?
Jonathan Gennick [EMAIL PROTECTED] wrote: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? The roll your own table thing is just about as bad as I've ever had and not really THAT portable. The serialisation problem is the same for ALL database architectures, you have to do non-portable tricks to solve the problem such as multiple rows for multiple ranges, each row in its block, or partition. Silly, really. GUID is too heavy on space. Just too long a key for practical purposes. And I don't care how cheap disks have become! I've used in the past things like Julian days + year + seconds in day + session number as seeds for GUIDs, but they all have potential problems. Now, I use mostly sequences, but with a twist. Surrogate keys almost exclusively. Because I work in environments that may require M-M replication later on, I have to generate globally unique keys. So, I always generate an instance table that stores the database name and a number associated to it, between 0 and 1000. This number is then SUFFIXED to all sequence -generated surrogate keys everywhere. This ensures globally unique keys at very low cost in processing and space overhead. And it will work in RAC as well! I got the idea from some replies in asktom. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do Souto 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).
RE: How do you genrate primary keys?
Except of course that internal employee ids also can get reused, and the converse the same individual can have more than one employee id. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TOMPKINS, MARGARET Sent: 05 November 2003 14:10 To: Multiple recipients of list ORACLE-L Subject: RE: How do you genrate primary keys? Social security numbers are notoriously bad natural primary keys. Did you know that they are re-used? Yes, it's true. Generally, they don't get re-issued until after one of the users dies, but it's been a problem in the past and still is. What do you do with people who don't have SSNs? Foreign nationals and others that work for US companies oversees or provide goods/services generally do NOT have SSNs. An internal employee id would be a much better choice if a natural primary key is needed. Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -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
RE: How do you genrate primary keys?
The thought is that if it is internal then you control it. Of course, it doesn't mean you will do it right. ;-) Maggie -Original Message- Sent: Wednesday, November 05, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Except of course that internal employee ids also can get reused, and the converse the same individual can have more than one employee id. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TOMPKINS, MARGARET Sent: 05 November 2003 14:10 To: Multiple recipients of list ORACLE-L Subject: RE: How do you genrate primary keys? Social security numbers are notoriously bad natural primary keys. Did you know that they are re-used? Yes, it's true. Generally, they don't get re-issued until after one of the users dies, but it's been a problem in the past and still is. What do you do with people who don't have SSNs? Foreign nationals and others that work for US companies oversees or provide goods/services generally do NOT have SSNs. An internal employee id would be a much better choice if a natural primary key is needed. Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -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
RE: How do you genrate primary keys?
Todd writes I'm a bit more concerned about what i'm hearing about Sequences. Is it true that sequences are NOT guaranteed to be unique?? As I understand it, and forbidding wraparound etc, sequences *are* guaranteed unique. They are not guaranteed - indeed in general won't be - gap free. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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).
RE: How do you genrate primary keys?
So,I tell the other Cher to change her name, or do I not enter her into the database? My point was that personnel data has no natural key and therefore cannot be fully protected from duplicate entries, but that the situation to which this applies are few in number. Ian MacGregor -Original Message- Sent: Wednesday, November 05, 2003 11:54 AM To: Multiple recipients of list ORACLE-L For entity uniqueness you have a unique identifier. You might even have more than one. For drawing entity relationship diagrams however, I don't know of any tool that allows you to display more than one, so you have a primary unique identifier and perhaps other unique identifiers that exist but don't show up on an ERD. When the entity gets transformed into a table, each of the unique identifiers should get implemented with a unique key constraint. That is the natural unique identifier on the entity becomes a unique key on the table. The table also gets the sequence generated surrogate primary key that we have been talking about. For the names you describe, some people only require one name like Cher and Madonna. If the unique key is made up of several components like first name, last name, etc. then you could have NULL for a last name to accommodate Cher and her friends. That works nicely in a unique key but of course, you can't have NULL as a component of a primary key. However, only one Cher would be allowed in the table. Maggie Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 1:15 PM To: Multiple recipients of list ORACLE-L No, you cannot. Most entities have natural primary keys. People are the exception not the rule. I am not advocating the use of natural keys as the primary keys of tables. I like to sue sequnece numbers for that purpose. However the natural key should be identified and enforced via a unique constraint. If you only have a sequenced-based primary key how do you protect against duplicate entries? We have that problem with our personnel data because all it has is such a key, and our physics collaborations are world-wide. Different transliterations, switching of first and last names, and individuals without surnames can make life interesting. We have one person who only has a surname. I would think think that must be confusing at home. Perhaps they use a system similar to that in the old joke about the folks in Welsh village: Jones, the baker; and Jones, the post; and Jones the We have a program which helps with these problems, but it does not totally prevent someone from being in the database twice for a short time. I'd hate to think what are database would be link if we didn't enforce natural keys on our other tables. Ian MacGregor Stanford Linear Accelerator Cenr -Original Message- Sent: Wednesday, November 05, 2003 10:34 AM To: Multiple recipients of list ORACLE-L 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
RE: How do you genrate primary keys?
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
Re: How do you genrate primary keys?
Hi: Selecting from a table to generate PK is not good, not mainly because of performance, but because of scalability. To generate unique PK, you have to do select max(pk_column) from tab for update , if this session does not commit, others cannot select. Without using for update, though other people can, this caused duplicate records. I have seen many customer using this kind of method to generate PK for that so called and useless contiueous PK. Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 12:24 AM Cary, If hitting a table that keeps a counter causes so many performance problems, I wonder why hitting sys.seq$ is much faster. I'd like to have some education on this Oracle magic. The only thing I can think of is that Oracle keeps some numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do that. Yong Huang --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. 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- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you 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
RE: How do you genrate primary keys?
Yep! Only one Cher. If one already existed, she would have to adopt an alias. Your point is actually very close to reality. I've worked with a person table that have a few hundred million rows. We came to realize that it didn't matter how many components you put together - that getting the uniqueness based on a name was difficult. We finally abandoned the idea and can have duplicates. The only thing that really makes it work is that these names come attached with other information that is more easily made unique. As long as the foreign keys point to the right rows, all is well. Maggie Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 4:14 PM To: Multiple recipients of list ORACLE-L So,I tell the other Cher to change her name, or do I not enter her into the database? My point was that personnel data has no natural key and therefore cannot be fully protected from duplicate entries, but that the situation to which this applies are few in number. Ian MacGregor -Original Message- Sent: Wednesday, November 05, 2003 11:54 AM To: Multiple recipients of list ORACLE-L For entity uniqueness you have a unique identifier. You might even have more than one. For drawing entity relationship diagrams however, I don't know of any tool that allows you to display more than one, so you have a primary unique identifier and perhaps other unique identifiers that exist but don't show up on an ERD. When the entity gets transformed into a table, each of the unique identifiers should get implemented with a unique key constraint. That is the natural unique identifier on the entity becomes a unique key on the table. The table also gets the sequence generated surrogate primary key that we have been talking about. For the names you describe, some people only require one name like Cher and Madonna. If the unique key is made up of several components like first name, last name, etc. then you could have NULL for a last name to accommodate Cher and her friends. That works nicely in a unique key but of course, you can't have NULL as a component of a primary key. However, only one Cher would be allowed in the table. Maggie Respectfully, Maggie Tompkins - CAD SQA Corporate Applications Division Technology Services Organization - Kansas City Defense Finance and Accounting Service 816-926-1117 (DSN 465); [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 05, 2003 1:15 PM To: Multiple recipients of list ORACLE-L No, you cannot. Most entities have natural primary keys. People are the exception not the rule. I am not advocating the use of natural keys as the primary keys of tables. I like to sue sequnece numbers for that purpose. However the natural key should be identified and enforced via a unique constraint. If you only have a sequenced-based primary key how do you protect against duplicate entries? We have that problem with our personnel data because all it has is such a key, and our physics collaborations are world-wide. Different transliterations, switching of first and last names, and individuals without surnames can make life interesting. We have one person who only has a surname. I would think think that must be confusing at home. Perhaps they use a system similar to that in the old joke about the folks in Welsh village: Jones, the baker; and Jones, the post; and Jones the We have a program which helps with these problems, but it does not totally prevent someone from being in the database twice for a short time. I'd hate to think what are database would be link if we didn't enforce natural keys on our other tables. Ian MacGregor Stanford Linear Accelerator Cenr -Original Message- Sent: Wednesday, November 05, 2003 10:34 AM To: Multiple recipients of list ORACLE-L 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
