It was a write ahead error :( > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Melanie Caffrey > Sent: 07 November 2003 22:50 > To: Multiple recipients of list ORACLE-L > Subject: RE: RE: How do you generate primary keys? > > > Ahh ... looks like you missed Henry Poras's reply, > Niall. > > He replied with the same answer for this one. ;) > > > --- Niall Litchfield <[EMAIL PROTECTED]> > wrote: > > Rollback? > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] On > > > Behalf Of Cary Millsap > > > Sent: 05 November 2003 20:39 > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: RE: How do you generate primary keys? > > > > > > > > > I've heard of people using instance startup > > triggers to > > > insert VOID records in cases where there are gaps. > > I haven't > > > thought about it much recently, but I can't > > presently think > > > of occasions when gaps occur other than instance > > shutdown. > > > > > > > > > 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----- > > > Jamadagni, Rajendra > > > Sent: Wednesday, November 05, 2003 12:14 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > Ryan, > > > > > > hypothetically, When you have a requirement that > > no gaps > > > allowed in a sequence no matter what, would you > > still use sequences? > > > > > > Raj > > > > > > -------------------------------------------------------------- > > > ---------- > > > -------- > > > Rajendra dot Jamadagni at nospamespn dot com > > > All Views expressed in this email are strictly > > personal. > > > QOTD: Any clod can have facts, having an opinion > > is an art ! > > > > > > > > > -----Original Message----- > > > 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 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 removed > > from). You may > > > also send > > > > the HELP command for other information (like > > subscribing). > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > > > -- > > > > Author: Cary Millsap > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com > > > > San Diego, California -- Mailing list and > > web > > > hosting services > > > > > > > > > > --------------------------------------------------------------------- > > > > To REMOVE yourself from this mailing list, send > > an E-Mail message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') and in > > > > the message BODY, include a line containing: > > UNSUB ORACLE-L (or the > > > > name of mailing list you want to be removed > > from). You may > > > also send > > > > the HELP command for other information (like > > subscribing). > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > > -- > > > Author: <[EMAIL PROTECTED] > > > 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). > > > > > > > > > ************************************************************** > > > ********** > > > ************** > > > This e-mail message is confidential, intended only > > for the named > > > recipient(s) above and may contain information > > that is > > > privileged, attorney work product or exempt from > > disclosure > > > under applicable law. If you have received this > > message in > > > error, or are not the named recipient(s), please > > immediately > > > notify corporate MIS at (860) 766-2000 and delete > > this e-mail > > > message from your computer, Thank you. > > > > > > ************************************************************** > > > ********** > > > **************4 > > > -- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > > -- > > > Author: Jamadagni, Rajendra > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com > > > San Diego, California -- Mailing list and > > web hosting services > > > > > > --------------------------------------------------------------------- > > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') > > > and in the message BODY, include a line > > containing: UNSUB > > > ORACLE-L (or the name of mailing list you want to > > be removed > > > from). You may also send the HELP command for > > other > > > information (like subscribing). > > > > > > -- > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > > -- > > > Author: Cary Millsap > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services -- 858-538-5051 > > http://www.fatcity.com > > > San Diego, California -- Mailing list and > > web hosting services > > > > > > --------------------------------------------------------------------- > > > To REMOVE yourself from this mailing list, send an > > E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of > > 'ListGuru') > > > and in the message BODY, include a line > > containing: UNSUB > > > ORACLE-L (or the name of mailing list you want to > > be removed > > > from). You may also send the HELP command for > > other > > > information (like subscribing). > > > > > > > -- > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.net > > -- > > Author: 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). > > > __________________________________ > 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: Melanie Caffrey > 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: 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).