Re: How do you genrate primary keys?

Jonathan

Yet another way [I do understand the drawbacks :)]:

• Table with the sequences "my_sequences" -- for preliminary definition sys.seq$ can be considered, if possible each row is placed into dedicated block (number of sequence does matter in this case so, it's a search for trade off) -- could help when there is a lot of concurrent requesters. Suppose number of such sequences defined as N.
• FUNCTION get_next_range('sequence', range) (AUTONOMOUS transaction) returns next number and updates the sequence number according to the requested range -- so a caller would get the small pool (session sub-pool) of sequences. 'SQ' can be emulated via dbms_lock, if needed, that would take additional time, of course.
• FUNCTION get_next('sequence') that would return next value from the pool of requested numbers -- it would not touch "my_sequences" unless it's out of numbers.
• FUNCTION get_current('sequence'), emulates .CURRVAL

For example, the caller knows that it would need 10 unique numbers for the given transaction:

1. get_next_range('COMMON_SEQUENCE', 10); -> 17 (if someone else call get_next_range now he would get 27)
2. INSERT INTO table_a (id, name) VALUES(get_next('COMMON_SEQUENCE', 'test'); etc.

Practically it does emulate regular sequences but it allows each caller to define its own pool, that could be a bit more efficient but requires some programming and accuracy.

Also, one can consider something like:

CHR(65 + MOD(SYS_CONTEXT('USERENV', 'SESSIONID'), N)) add this prefix and make primary keys e.g. character based (or play around and stick with numeric values only) using the approach described above. It would make particular sessions based on particular sequence entry of my_sequences table.

Gaps are unavoidable here.

*Drawbacks* are *clear*, so it's just an idea that can be implemented and tested. I'm pretty sure that the approach is disputable, so please let's avoid long discussions.

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

Jonathan Gennick 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,



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Vladimir Begun
  INET: Vladimir.Begun_at_oracle.com

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: ListGuru_at_fatcity.com (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).