Using remote sequences
A couple of the replies took the question a bit too literally, and said yes, you can define a sequence on one database, and use it from another:
Select SequenceOwner.RemoteSeqName.NextVal@DBLinkName From dual
This works - and it can be made 'location transparent' by creating a local synonym for the remote sequence. But it is asymmetric (one database has to own the sequence) and it introduces a point of failure. If database 1 owns the sequence, database 2 can only insert rows if database 1 is actually available. If you could guarantee availability, why would you bother with replication?
Using a sequence local to each database
Several replies (including mine) suggested using carefully defined sequences which will deliver discrete values on two or more master databases. There are two basic patterns:
Partitioned: Suggested by several posters, the number space is divided up in blocks:
on db 1: create sequence myseq start with 1000000 max 1999999
on db 2: create sequence myseq start with 2000000 max 2999999
A variant on this is to use positive numbers for database 1, and negative numbers for database 2.
Interleaved: The more popular option is to use the old trick of assigning odd numbers to database 1, and even numbers to database 2:
on db 1: create sequence myseq start with 1 increment by 2
on db 2: create sequence myseq start with 2 increment by 2
This mechanism is much easier to manage (essentially, it doesn't need any further management). It is also easy to extend to 3, 4, 27 or 127 masters - just set the "start with" to the database number, and "increment by" to the maximum anticipated number of databases required.
A third option was also proposed by Mark D Powell: use SYS_GUID(). That has some disadvantages:
- SYS_GUID() is bigger (16 bytes on 9iR2) than a NUMBER (eg a 13 digit integer needs around 8 bytes). Obviously the extra space follows through to indexes, foreign keys etc.
- Another is simply that it is a RAW, which has some possibly undesirable implications; for example several common tools (including SQL*Developer 1.0) can't directly display RAW values; you have to explicitly select RAWTOHEX(id_column).
On the other hand, SYS_GUID can be defined as the default value for a column - unlike NEXTVAL which is normally set in a trigger or directly as part of an insert into/select from statement. Worse, it is very common to see a separate SELECT seq.NEXTVAL FROM DUAL for every ID generated. I've never investigated the relative performance of SYS_GUID() against getting a sequence number - anyone else like to share that? That may well be the most important consideration of all.
So I think I'll be coming back to this subject in future.
Update - later the same day:
I've quickly timed a million iterations of select sys_guid() from dual, and a million iterations of select sequence.nextval from dual (Oracle XE 10.2.0.1, HP dv1665 Centrino Duo running Windows XP)
select sys_guid() into variable from dual: 89 seconds
select seq.nextval into variable from dual: 40 seconds
For good measure, I've added a variant - one million calls of sys_guid() without a select:
variable := sys_guid() : 95 seconds
One of the potential advantages of sys_guid() I had anticipated is that it could be called directly from PL/SQL - but it looks like the implementation is less efficient than it might be; tracing shows that the PL/SQL function recursively selects SYS_GUID() from dual.
So I think I'll stick to traditional and more convenient sequence.nextval for now.