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 number_of datafiles + 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 modegrin. 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
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[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 stand managers. For example: 1001 John A. Smith 1002 Dave Smith 1003 John David 1004 John Q. Smith etc. I suppose we could have hidden these ID numbers from the users, but I'm not sure that would have been practical. ID numbers represent a fairly unambiguous way to reference people. Asking about John Smith is ambiguous, but asking about manager 1004 is not. I suppose we could have generated two numbers, one hidden and one for the user to see, as in: 1, 1001, John A.Smith 2, 1002, Dave Smith, etc. And then the 1,2,... would be the primary key, hidden from the user, and 1001, 1002 would be the IDs the users saw. But this solution seems overly complex. For the system I speak of, we generated the IDs from a sequence, assigned them when manager records were created, and we never had anyone decide to give a manager a different ID number. The key to success, in this, is likely that it was *our* system that *generated* the keys. With something like social security number, your not generating the key, but rather you are dependent on someone else for the value, and thus you should *not* use the social security number as a key. Instead, you should generate your own key, and leave social security number as an attribute. 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[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 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 modegrin. 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).
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, 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 modegrin. 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). 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).
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 modegrin. 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).
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 modegrin. 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?
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 modegrin. 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, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading
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?
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 modegrin. 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
