| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How do you genrate primary keys?
('binary' encoding is not supported, stored as-is)
> Jonathan Gennick <jonathan_at_gennick.com> wrote:
>
> What mechanisms have you used to generate primary keys?
> Which ones worked well, and why? Which mechanisms worked
> poorly?
>
The "roll your own table" thing is just about as bad as I've ever had and not really THAT portable. The serialisation problem is the same for ALL database architectures, you have to do non-portable tricks to solve the problem such as multiple rows for multiple ranges, each row in its block, or partition. Silly, really.
GUID is too heavy on space. Just too long a key for practical purposes. And I don't care how cheap disks have become!
I've used in the past things like Julian days + year + seconds in day + session number as "seeds" for GUIDs, but they all have potential problems.
Now, I use mostly sequences, but with a twist. Surrogate keys almost exclusively. Because I work in environments that may require M-M replication later on, I have to generate globally unique keys. So, I always generate an "instance" table that stores the database name and a number associated to it, between 0 and 1000. This number is then SUFFIXED to all sequence -generated surrogate keys everywhere.
This ensures globally unique keys at very low cost in processing and space overhead. And it will work in RAC as well! I got the idea from some replies in asktom.
Cheers
Nuno Souto
nsouto_at_wizofoz2k.com.au
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do Souto INET: nsouto_at_optusnet.com.au 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).Received on Wed Nov 05 2003 - 14:44:26 CST
 |
 |