Re: Re[3]: How do you generate primary keys?
I think this is a high-level design and even analysis issue in many cases. If no gaps are allowed, you probably should allocate a key value for any record just before committing the transaction (commiting has wider meaning here, e.g. accepting or permanently storing). That way you won't get problems with rollbacks, but of course in some environments you have to know your keys ID before you commit or IDs should be allocated based on transaction start time etc. In these cases you have to do some kind of tradeoff between performance and key quality and these kind of decisions should be done as early as possible, in analysis/design stage. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 4:14 PM Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[3]: How do you generate primary keys?
Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. 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: Re[3]: How do you generate primary keys?
The only reliable solution in this case is to serialize which of course means forget about scaling. On Thu, 2003-11-06 at 09:14, Jonathan Gennick wrote: Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. 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 Richard Quintin, DBA Information Systems Computing, DBMS Virginia Tech -- If you would stand well with a great mind, leave him with a favorable impression of yourself; if with a little mind, leave him with a favorable impression of himself. -- Samuel Taylor Coleridge -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Quintin, Richard 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[3]: How do you generate primary keys?
Haven't tried this but how does this sound: Use a sequence cache it - to allow scalability pin it so it doesn't age out of the cache have a shutdown trigger make the sequence nocache so don't lose values on a clean shutdown That leaves instance crashes and shutdown aborts to worry about So, create a startup trigger that resets the current value of all sequences to the next correct value For speed, the shutdown trigger could leave a flag somewhere so that the startup trigger only tried to process if it needed to There's probably flaws in this so certainly test, test and test. HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Friday, 7 November 2003 1:15 AM To: Multiple recipients of list ORACLE-L Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. 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: Reardon, Bruce (CALBBAY) 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[3]: How do you generate primary keys?
And to reply to my own message - the flaw is rollbacks I guess you could use a savepoint after the sequence number, and if you want to undo the transaction put the record in an unused record history table, but all very messy. Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Friday, 7 November 2003 8:34 AM To: Multiple recipients of list ORACLE-L Haven't tried this but how does this sound: Use a sequence cache it - to allow scalability pin it so it doesn't age out of the cache have a shutdown trigger make the sequence nocache so don't lose values on a clean shutdown That leaves instance crashes and shutdown aborts to worry about So, create a startup trigger that resets the current value of all sequences to the next correct value For speed, the shutdown trigger could leave a flag somewhere so that the startup trigger only tried to process if it needed to There's probably flaws in this so certainly test, test and test. HTH, Bruce Reardon NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Friday, 7 November 2003 1:15 AM To: Multiple recipients of list ORACLE-L Wednesday, November 5, 2003, 1:14:26 PM, Jamadagni, Rajendra ([EMAIL PROTECTED]) wrote: JR hypothetically, When you have a requirement that no gaps allowed in a sequence no matter what, JR would you still use sequences? Ah! This is a good question. If no gaps are acceptable, period, end of story, then what is a viable solution? I do not think sequences are it. Best regards, Jonathan Gennick --- Brighten the corner where you are -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Reardon, Bruce (CALBBAY) 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).
