> Jonathan Gennick <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do Souto 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).
