Re: How do you genrate primary keys?

: @yahoo.com Subject: Re: How do you genrate primary keys? Sent by: ml-errors 11/05/2003 09:44 AM Please respond

AW: How do you genrate primary keys?

, 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

Re: AW: How do you genrate primary keys?

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

RE: How do you genrate primary keys?

-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

Re: Re[2]: How do you genrate primary keys?

Hi! Well, if the commit is not too frequent, one natural way of generating primary key would be select max(last_change#) from v$datafile; No, you definitely don't want to do that!!! v$datafile uses x$kccf% tables which cause several physical reads into controlfiles and x$kcvfh which shows

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

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

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

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

Re: Re[2]: How do you genrate primary keys?

Dangerous. The UPDATE is not the same as a SELECT with lock. It has a read component that won't lock and a write component that WILL lock at write time. That is not what you want. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - What about doing it in one step? Declare

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

Re: How do you genrate primary keys?

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

Re[2]: How do you genrate primary keys?

Wednesday, November 5, 2003, 8:39:24 AM, Mercadante, Thomas F ([EMAIL PROTECTED]) wrote: MTF First, to me, a primary key should not be something that a user would ever MTF see or use. I'm not sure that's always practical. I once worked on a system that arbitrarily assigned ID numbers to vending

Re[2]: How do you genrate primary keys?

Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale ([EMAIL PROTECTED]) wrote: HKC 1. Hit a table that keeps a counter. HKC Used to be a mechanism in the Oracle5 days [If I remember correctly, HKC Sequences came in Oracle6]. Issues were with locking the single HKC record used as the

Re[2]: How do you genrate primary keys?

Yes, now I understand your concurrency issue. There would have been better ways but it was wiser not to spend time trying to improve going down the wrong path. Good that you convinced the managers there to go for Sequences early. Regards Hemant At 05:59 AM 06-11-03 -0800, you wrote: Wednesday,

RE: Re[2]: How do you genrate primary keys?

What about doing it in one step? Declare lCounter int; Begin UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name' RETURNING counter INTO lCounter; End; / Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Jonathan Gennick Sent: Thursday, November 06,

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.

RE: Re[2]: How do you genrate primary keys?

The implementations I've seen all did SELECT...FOR UPDATE. Works. Doesn't scale. 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

Re: Re[2]: How do you genrate primary keys?

Well, if the commit is not too frequent, one natural way of generating primary key would be select max(last_change#) from v$datafile; These numbers are guaranteed to be unique. As DBWR wakes up every 3 seconds, the intervals between commit should be at least 3 seconds. As you're very well aware,

Re: Re[2]: How do you genrate primary keys?

Jonathan Gennick [EMAIL PROTECTED] wrote: My concurrency issues probably boil down to the locking business. Purrcisely. because they would both issue the SELECT before either one got around to the UPDATE. I couldn't screw things up There you go. You are supposed to LOCK the row on the

RE: How do you genrate primary keys?

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

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)

Re: How do you genrate primary keys?

-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

Re: Re[2]: How do you genrate primary keys?

Mladen, If the session doesn't start a transaction, I don't see how this can work. First, multiple sessions can find the same SCN, no matter what kind of SCN you're talking about. Secondly, v$datafile.last_change# is set to null unless the datafile is offline. If each session has its own

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

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

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

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:

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

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?

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

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

Re: How do you genrate primary keys?

: Sent by: Subject: How do you genrate primary keys? ml-errors

Re: RE: How do you genrate primary keys?

. 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

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 -

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

RE: How do you genrate primary keys?

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

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

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

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

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

RE: RE: How do you genrate primary keys?

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

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

Re: RE: How do you genrate primary keys?

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

Re: How do you genrate primary keys?

: Sent by: Subject: How do you genrate primary keys? ml-errors

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

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.

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

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

RE: RE: How do you genrate primary keys?

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

RE: RE: How do you genrate primary keys?

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

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

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

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

Re: How do you genrate primary keys?

: @yahoo.com Subject: Re: How do you genrate primary keys? Sent by: ml-errors

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

Re: How do you genrate primary keys?

To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100 cc: @yahoo.com Subject: Re: How do you genrate

RE: RE: How do you genrate primary keys?

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

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:

RE: RE: How do you genrate primary keys?

: 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

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

RE: How do you genrate primary keys?

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

RE: How do you genrate primary keys?

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

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.

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

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

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

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