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: [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).
