RE: Generating Primary Key in Oracle (part 2)
Having a sequence facilitates the currval and nextval functions. If you are new to Oracle sequences I would read up on these two functions. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
Hi Stacy, I was thinking of using a single db table with single db column to store the current 'highest value' for the primary key. If anyone's used this method before...do you this incremental value across multiple tables? i.e. I have a table for 'orders' and another for 'users'. Would both feed off this single primary key table? I use a sequence, but I use the same sequence across an application. It cuts down on the number of objects I need to maintain, and makes it so that any one table probably doesn't have too many consecutive primary keys. The same would apply if you're building your own table to generate a sequential primary key. Though, I really see no reason for building your own table when sequences are so easy to use. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generating Primary Key in Oracle (part 2)
If your on Oracle, you should go with sequences! http://www.macromedia.com/devnet/server_archive/articles/cf_best_practices_oracle.html#6 Gabriel -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Friday, December 05, 2003 8:55 AM To: CF-Talk Subject: Re: Generating Primary Key in Oracle (part 2) Hi Stacy, I was thinking of using a single db table with single db column to store the current 'highest value' for the primary key. If anyone's used this method before...do you this incremental value across multiple tables? i.e. I have a table for 'orders' and another for 'users'. Would both feed off this single primary key table? I use a sequence, but I use the same sequence across an application. It cuts down on the number of objects I need to maintain, and makes it so that any one table probably doesn't have too many consecutive primary keys. The same would apply if you're building your own table to generate a sequential primary key. Though, I really see no reason for building your own table when sequences are so easy to use. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generating Primary Key in Oracle (part 2)
Thanks Deanna, it was a unique situation where the db might have been migrated mid-project to another platform so I was trying to keep it portable. As it stands I'll probably just use sequences. Cheers! Stace _ From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: December 5, 2003 8:55 AM To: CF-Talk Subject: Re: Generating Primary Key in Oracle (part 2) Hi Stacy, I was thinking of using a single db table with single db column to store the current 'highest value' for the primary key. If anyone's used this method before...do you this incremental value across multiple tables? i.e. I have a table for 'orders' and another for 'users'. Would both feed off this single primary key table? I use a sequence, but I use the same sequence across an application. It cuts down on the number of objects I need to maintain, and makes it so that any one table probably doesn't have too many consecutive primary keys. The same would apply if you're building your own table to generate a sequential primary key. Though, I really see no reason for building your own table when sequences are so easy to use. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generating Primary Key in Oracle (part 2)
Deanna wrote: I use a sequence, but I use the same sequence across an application. It cuts down on the number of objects I need to maintain, and makes it so that any one table probably doesn't have too many consecutive primary keys. That is very interesting I've always had a pk sequence for (just about) every table rather than just having one and using that everywhere. Ever come across a situation where this 'one sequence fits all' has tripped you up? Anyone else got any advantages/disadvantages with regards to this? -- I now know how to delimit my sig :-) -dc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
I haven't had a problem with it yet. The only problem I could imagine is maxing out the sequence, but since they go pretty insanely high, and none of our projects have that kind of total data accumulation, I haven't been worried about that. I suppose if you were doing a lot of transactions in the database, you might run into some locking issues. But, we generally only have a web front end - so we're not dealing with locked transactions at all. -Deanna - Original Message - From: [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, December 05, 2003 8:53 AM Subject: RE: Generating Primary Key in Oracle (part 2) Deanna wrote: I use a sequence, but I use the same sequence across an application. It cuts down on the number of objects I need to maintain, and makes it so that any one table probably doesn't have too many consecutive primary keys. That is very interesting I've always had a pk sequence for (just about) every table rather than just having one and using that everywhere. Ever come across a situation where this 'one sequence fits all' has tripped you up? Anyone else got any advantages/disadvantages with regards to this? -- I now know how to delimit my sig :-) -dc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generating Primary Key in Oracle (part 2)
Deanna wrote: The only problem I could imagine is maxing out the sequence, but since they go pretty insanely high, and none of our projects have that kind of total data accumulation, I haven't been worried about that. I like, I like.As you mention, wouldn't be able to do it all the time but for a smaller app... I think I'd be well tempted to use :-) -- -dc [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generating Primary Key in Oracle (part 2)
An example of where a common seq would cause problems is if the seq number becomes an invoice number, and the audit trail requires that every number in a sequence be accounted for.This is pretty outmoded thinking now, but some auditors still stick to the rule - I want to see every invoice,IN SEQUENCE, filed in those binders there to verify they are all accounted for. Cheers, Michael Kear Windsor, NSW, Australia AFP Webworks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, 6 December 2003 1:54 AM To: CF-Talk Subject: RE: Generating Primary Key in Oracle (part 2) Deanna wrote: I use a sequence, but I use the same sequence across an application. It cuts down on the number of objects I need to maintain, and makes it so that any one table probably doesn't have too many consecutive primary keys. That is very interesting I've always had a pk sequence for (just about) every table rather than just having one and using that everywhere. Ever come across a situation where this 'one sequence fits all' has tripped you up? Anyone else got any advantages/disadvantages with regards to this? -- I now know how to delimit my sig :-) -dc _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
Blah! That would be an issue. But, it's not one that I'll ever have to deal with, as we don't do accounting type stuff. But, good to know for future reference. An example of where a common seq would cause problems is if the seq number becomes an invoice number, and the audit trail requires that every number in a sequence be accounted for.This is pretty outmoded thinking now, but some auditors still stick to the rule - I want to see every invoice,IN SEQUENCE, filed in those binders there to verify they are all accounted for. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
An Oracle sequence is not guaranteed to include every number so you would never want to use to for invoice numbers if you need to account for every value. An example of when a sequence number might be lost is if a transaction grabs a number and then does a rollback instead of committing. Using one sequence can cause a resource contention, a Hot Spot, issue if you have too many things trying to access the sequence at similar times. If you are worried about a B-tree index that is adding nearly sequential data from becoming out of balance or lopsided you can use a reverse key index in Oracle. -Kore Peterson Deanna Schneider [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] .uwex.educc: Subject:Re: Generating Primary Key in Oracle (part 2) 12/05/2003 11:03 AM Please respond to cf-talk Blah! That would be an issue. But, it's not one that I'll ever have to deal with, as we don't do accounting type stuff. But, good to know for future reference. An example of where a common seq would cause problems is if the seq number becomes an invoice number, and the audit trail requires that every number in a sequence be accounted for.This is pretty outmoded thinking now, but some auditors still stick to the rule - I want to see every invoice,IN SEQUENCE, filed in those binders there to verify they are all accounted for. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
If what you're trying to accomplish is to figure out what the most recently inserted value is (or, more accurately, the one inserted by your session) you might find this article useful: http://www.markme.com/cantrell/archives/002201.cfm Basically you create a surrogate pk via a uuid prior to your insert, and then use that value to query back the db for the numeric ID that just got created (via a sequence, since this is Oracle).This lets you use a nice slim numeric pk and skip all the other gymnastics (locks, transaction blocks etc.) you would need otherwise.All at the cost of a single text field and index. -- --- Matt Robertson,[EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com --- -- [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
What's wrong with using a sequence? - Original Message - From: Stacy Young [EMAIL PROTECTED] Date: Thursday, December 4, 2003 6:25 pm Subject: Generating Primary Key in Oracle (part 2) I was thinking of using a single db table with single db column to storethe current 'highest value' for the primary key. If anyone's used this method before...do you this incremental value across multiple tables? i.e. I have a table for 'orders' and another for 'users'. Would both feed off this single primary key table? Any downsides to this? I need to avoid the use of any triggers or storedprocs in this project. I'm so tempted to just use UUIDs...but the amount of data can potentially grow quite large and I'm a little worried aboutthe join performance with UUIDs. Any opinions appreciated Stace AVIS IMPORTANT: --- Les informations contenues dans le present document et ses pieces jointes sont strictement confidentielles et reservees a l'usage de la (des) personne(s) a qui il est adresse. Si vous n'etes pas le destinataire, soyez avise que toute divulgation, distribution, copie, ou autre utilisation de ces informations est strictement prohibee. Si vous avez recu ce document par erreur, veuillez s'il vous plait communiquer immediatement avec l'expediteur et detruire ce document sans en faire de copie sous quelque forme. WARNING: --- The information contained in this document and attachments is confidential and intended only for the person(s) named above. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution, or any other use of the information is strictly prohibited. If you have received this document by mistake, please notify the sender immediately and destroy this document and attachments without making any copy of any kind. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
[EMAIL PROTECTED] wrote: What's wrong with using a sequence? maybe that's too obvious.Although honestly I've never learned how to create sequences in oracle.My primary keys tend to be either integers where I run a transaction to request the max value then insert the next... or I just get lazy and use a char field and insert a Cold Fusion UUID. =) - Rick [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generating Primary Key in Oracle (part 2)
My primary keys tend to be either integers where I run a transaction to request the max value then insert the next... This is an absolutely horrible way to get a new id. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Generating Primary Key in Oracle (part 2)
Rick wrote: I've never learned how to create sequences in oracle Its easy, actually.On a table that exists named my_filename, create it like this: cfquery datasource=#request.myDSN# CREATE SEQUENCE seq_my_filename INCREMENT BY 1 START WITH 1 MINVALUE 0 CACHE 10; /cfquery The sequence name is just the filename with seq_ prepended to it. This is a convention I made up; probably everyone does it I bet.Every sequence must have a unique name, just like an Oracle index. Then when you insert something, you insert the value like so: cfquery datasource=#request.myDSN# INSERT INTO my_filename ( ID, myCharField ) VALUES ( seq_my_filename.nextval, cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#variables.TheValueFilter# ) /cfquery HtH, Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
