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 datafile header information for every datafile in your database, causing one additional physical IO per datafile! None of those IOs are cached by Oracle. So, if you used the v$datafile approach, you'd be getting + about 10 physical IOs for single PK value generation! Yong already commented on the other issues with v$datafile usage. Tanel. > aware, this is the natural mechanism that ensures that any change is properly > enumerated and, thus, the best and most generic primary key. I understand that > someone might doubt this mechanism as I would never even dream of using it, > but SCN is the thing that comes naturally. Alternatively, one could produce SCN > from V$TRANSACTION (base + wrap). > > > On 11/06/2003 12:54:38 PM, Cary Millsap wrote: > > 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 www.hotsos.com for schedule details... > > > > > > -Original Message- > > Jonathan Gennick > > Sent: Thursday, November 06, 2003 7:59 AM > > To: Multiple recipients of list ORACLE-L > > > > 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 generator or scanning for the max(value) of the > > HKC> key. > > HKC> Not quite sure I understand how you encountered concurrency issues, > > though. > > > > My concurrency issues probably boil down to the locking > > business. The app I'm thinking of originally did something > > like: > > > > SELECT counter INTO :1 > > FROM counter_table > > WHERE counter_name = 'table name'; > > > > ...some app code goes here... > > > > UPDATE counter_table > > SET counter := counter+1 > > WHERE counter_name = 'table name'; > > > > Well, it all worked fine in single-user mode. But it > > was easy enough for me to sit down in front of two > > computers, create two new records, press SAVE at the same > > time, and cause two sessions to grab the same key value, > > because they would both issue the SELECT before either one > > got around to the UPDATE. I couldn't screw things up > > consistently, but just by hitting the SAVE button at the > > same time I could screw things up often enough to make the > > problem obvious. > > > > Maybe there's a way to lock the table, to make the above > > approach work. In my case, I didn't bother trying to find > > that solution. Once I did my little demo, it was easy enough > > to convince the project manager that we should switch to > > using Oracle sequences. > > > > 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: 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 rem
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 lCounter int; > Begin > UPDATE counter_table > SET counter := counter+1 > WHERE counter_name = 'table name' > RETURNING counter INTO lCounter; > End; -- 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: 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 transaction, i.e. not just a regular query, then their own SCN's at the time the transaction started as shown in v$transaction may be used as a uniqur identifier. But the session can't use a savepoint and hope that has another number in v$transaction; that's not a common requirement though. Yong Huang --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > 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, this is the natural mechanism that ensures that any change is properly > > enumerated and, thus, the best and most generic primary key. I understand > that > someone might doubt this mechanism as I would never even dream of using it, > but SCN is the thing that comes naturally. Alternatively, one could produce > SCN > from V$TRANSACTION (base + wrap). > > > On 11/06/2003 12:54:38 PM, Cary Millsap wrote: > > 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 www.hotsos.com for schedule details... > > > > > > -Original Message- > > Jonathan Gennick > > Sent: Thursday, November 06, 2003 7:59 AM > > To: Multiple recipients of list ORACLE-L > > > > 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 generator or scanning for the max(value) of the > > HKC> key. > > HKC> Not quite sure I understand how you encountered concurrency issues, > > though. > > > > My concurrency issues probably boil down to the locking > > business. The app I'm thinking of originally did something > > like: > > > > SELECT counter INTO :1 > > FROM counter_table > > WHERE counter_name = 'table name'; > > > > ...some app code goes here... > > > > UPDATE counter_table > > SET counter := counter+1 > > WHERE counter_name = 'table name'; > > > > Well, it all worked fine in single-user mode. But it > > was easy enough for me to sit down in front of two > > computers, create two new records, press SAVE at the same > > time, and cause two sessions to grab the same key value, > > because they would both issue the SELECT before either one > > got around to the UPDATE. I couldn't screw things up > > consistently, but just by hitting the SAVE button at the > > same time I could screw things up often enough to make the > > problem obvious. > > > > Maybe there's a way to lock the table, to make the above > > approach work. In my case, I didn't bother trying to find > > that solution. Once I did my little demo, it was easy enough > > to convince the project manager that we should switch to > > using Oracle sequences. > > > > 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: 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
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 SELECT, then you grab the key and update it as fast as possible. That takes care of the "same key for 2 users" issue. Then you gotta solve the scalability probem. Here, two rules: 1- Make the lock period as small as possible. Only grab the key RIGHT before you're ready to commit the new row. NOT at the start of the transaction. 2- Spread the load. Put one row in the lock table for every 1 key values, make each row into one block. Now, select in round-robin fashion from one of the rows. That spreads the load across rows/blocks in the lock table. All par for the course in databases where sequences a-la Oracle do not exist. Of course, if you use Oracle and you still do all this, you're asking for trouble... And I strongly disagree that a PK ever has to be consecutive (with no gaps). That is NOT a PK. That may be a unique key or some other business requirement, but it is most definitely not a PK. 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: 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, this is the natural mechanism that ensures that any change is properly enumerated and, thus, the best and most generic primary key. I understand that someone might doubt this mechanism as I would never even dream of using it, but SCN is the thing that comes naturally. Alternatively, one could produce SCN from V$TRANSACTION (base + wrap). On 11/06/2003 12:54:38 PM, Cary Millsap wrote: > 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 www.hotsos.com for schedule details... > > > -Original Message- > Jonathan Gennick > Sent: Thursday, November 06, 2003 7:59 AM > To: Multiple recipients of list ORACLE-L > > 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 generator or scanning for the max(value) of the > HKC> key. > HKC> Not quite sure I understand how you encountered concurrency issues, > though. > > My concurrency issues probably boil down to the locking > business. The app I'm thinking of originally did something > like: > > SELECT counter INTO :1 > FROM counter_table > WHERE counter_name = 'table name'; > > ...some app code goes here... > > UPDATE counter_table > SET counter := counter+1 > WHERE counter_name = 'table name'; > > Well, it all worked fine in single-user mode. But it > was easy enough for me to sit down in front of two > computers, create two new records, press SAVE at the same > time, and cause two sessions to grab the same key value, > because they would both issue the SELECT before either one > got around to the UPDATE. I couldn't screw things up > consistently, but just by hitting the SAVE button at the > same time I could screw things up often enough to make the > problem obvious. > > Maybe there's a way to lock the table, to make the above > approach work. In my case, I didn't bother trying to find > that solution. Once I did my little demo, it was easy enough > to convince the project manager that we should switch to > using Oracle sequences. > > 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: 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). > Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose,
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 www.hotsos.com for schedule details... -Original Message- Jonathan Gennick Sent: Thursday, November 06, 2003 7:59 AM To: Multiple recipients of list ORACLE-L 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 generator or scanning for the max(value) of the HKC> key. HKC> Not quite sure I understand how you encountered concurrency issues, though. My concurrency issues probably boil down to the locking business. The app I'm thinking of originally did something like: SELECT counter INTO :1 FROM counter_table WHERE counter_name = 'table name'; ...some app code goes here... UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name'; Well, it all worked fine in single-user mode. But it was easy enough for me to sit down in front of two computers, create two new records, press SAVE at the same time, and cause two sessions to grab the same key value, because they would both issue the SELECT before either one got around to the UPDATE. I couldn't screw things up consistently, but just by hitting the SAVE button at the same time I could screw things up often enough to make the problem obvious. Maybe there's a way to lock the table, to make the above approach work. In my case, I didn't bother trying to find that solution. Once I did my little demo, it was easy enough to convince the project manager that we should switch to using Oracle sequences. 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: 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).
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, 2003 8:59 AM To: Multiple recipients of list ORACLE-L 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 generator or scanning for the max(value) of the HKC> key. HKC> Not quite sure I understand how you encountered concurrency issues, though. My concurrency issues probably boil down to the locking business. The app I'm thinking of originally did something like: SELECT counter INTO :1 FROM counter_table WHERE counter_name = 'table name'; ...some app code goes here... UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name'; Well, it all worked fine in single-user mode. But it was easy enough for me to sit down in front of two computers, create two new records, press SAVE at the same time, and cause two sessions to grab the same key value, because they would both issue the SELECT before either one got around to the UPDATE. I couldn't screw things up consistently, but just by hitting the SAVE button at the same time I could screw things up often enough to make the problem obvious. Maybe there's a way to lock the table, to make the above approach work. In my case, I didn't bother trying to find that solution. Once I did my little demo, it was easy enough to convince the project manager that we should switch to using Oracle sequences. 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: Igor Neyman 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).
