ORACLE globally unique id's

ORACLE globally unique id's

Post by Ankur Gup » Wed, 21 Mar 2001 23:47:51



I was wondering whether Oracle 8i supported globally unique ids? Is there any way they
can be auto-generated in Oracle?? or any other utility I can refer to?

--
-Ankur Gupta

 
 
 

ORACLE globally unique id's

Post by Todd Gillespi » Thu, 22 Mar 2001 00:15:05


: I was wondering whether Oracle 8i supported globally unique ids? Is there any way they
: can be auto-generated in Oracle?? or any other utility I can refer to?

My method:
create sequence global_id_seq;
POLICY: "Every table fill their ids from this sequence."
There; global unique ids with a minimum of application overhead.

Were you looking for something closer to Oracle?  I strongly suspect that
you cannot define uniqueness constraints across all tables, or a
tablespace, or whatever.  Perhaps you could store a table of allocated
global ids, and define a BEFORE INSERT TRIGGER on each table, which rolls
back a row if the row's id is taken.  Major, major overhead, though.

Maybe you could better specify the functionality you're aiming at?

 
 
 

ORACLE globally unique id's

Post by Ankur Gup » Thu, 22 Mar 2001 00:40:07



: : I was wondering whether Oracle 8i supported globally unique ids? Is there any way they
: : can be auto-generated in Oracle?? or any other utility I can refer to?

: My method:
: create sequence global_id_seq;
: POLICY: "Every table fill their ids from this sequence."
: There; global unique ids with a minimum of application overhead.

: Were you looking for something closer to Oracle?  I strongly suspect that
: you cannot define uniqueness constraints across all tables, or a
: tablespace, or whatever.  Perhaps you could store a table of allocated
: global ids, and define a BEFORE INSERT TRIGGER on each table, which rolls
: back a row if the row's id is taken.  Major, major overhead, though.

: Maybe you could better specify the functionality you're aiming at?

The main problem here I am trying to address is the following:

Currently I am using a oracle sequence number to generate an id. I have couple of
databases running at remote locations which have the same schema. Data migration
happens between these databases. Since the seq num generated by oracle isn't
globally unique it causes a conflict during migration. So my main aim is to devise
a better method of generating keys. what i was trying to address was maybe oracle
would have addressed this problem.. the solution in ur case works but causes a
bottleneck on that globally unqiue seq. table.

--
-Ankur Gupta

 
 
 

ORACLE globally unique id's

Post by Todd Gillespi » Thu, 22 Mar 2001 06:56:01


: Currently I am using a oracle sequence number to generate an id. I have couple of
: databases running at remote locations which have the same schema. Data migration
: happens between these databases. Since the seq num generated by oracle isn't
: globally unique it causes a conflict during migration. So my main aim is to devise
: a better method of generating keys. what i was trying to address was maybe oracle
: would have addressed this problem.. the solution in ur case works but causes a
: bottleneck on that globally unqiue seq. table.

1. Can you have a dblink between the 2 DBs and agree that one of them
controls the sequence object?  Network latency constraints, but you
already have migration running...

2. INTEGER in Oracle is a 38-digit, base-10 number.  That's a lot of
keys.  Maybe for N migration shared databases, have N local sequence
objects, which start at 1 + ((10^38 - 1)/N)*(N-1)?  It would be a _long_
time before the different DB step on toes.

3. How it all worked out for me was exchanging data between
non-migrating oracles via XML, and I wrote up some complex code to compare
& reassign ids to any conflicting rows.  (or in some cases, change INSERTs
into UPDATEs.  Of course, this approach is also the least general.

I may be kicking a dead horse here, but I think you can solve this problem
with just the standard tools and some tomfoolery.

(btw: I would *never* write the 'global_assigned_ids' table -- imagine the
perf hit!!  It was just a gedankenkludge.)

 
 
 

ORACLE globally unique id's

Post by Alan » Thu, 22 Mar 2001 23:21:45


Composite or concatanated key. Use a three letter identifier to create
uniqueness.


Quote:> I was wondering whether Oracle 8i supported globally unique ids? Is there
any way they
> can be auto-generated in Oracle?? or any other utility I can refer to?

> --
> -Ankur Gupta

 
 
 

ORACLE globally unique id's

Post by Ankur Gup » Sat, 24 Mar 2001 00:24:51


Found function SYS_GUID() in Oracle that does the trick...Thanks for your answers.


: : Currently I am using a oracle sequence number to generate an id. I have couple of
: : databases running at remote locations which have the same schema. Data migration
: : happens between these databases. Since the seq num generated by oracle isn't
: : globally unique it causes a conflict during migration. So my main aim is to devise
: : a better method of generating keys. what i was trying to address was maybe oracle
: : would have addressed this problem.. the solution in ur case works but causes a
: : bottleneck on that globally unqiue seq. table.

: 1. Can you have a dblink between the 2 DBs and agree that one of them
: controls the sequence object?  Network latency constraints, but you
: already have migration running...

: 2. INTEGER in Oracle is a 38-digit, base-10 number.  That's a lot of
: keys.  Maybe for N migration shared databases, have N local sequence
: objects, which start at 1 + ((10^38 - 1)/N)*(N-1)?  It would be a _long_
: time before the different DB step on toes.

: 3. How it all worked out for me was exchanging data between
: non-migrating oracles via XML, and I wrote up some complex code to compare
: & reassign ids to any conflicting rows.  (or in some cases, change INSERTs
: into UPDATEs.  Of course, this approach is also the least general.

: I may be kicking a dead horse here, but I think you can solve this problem
: with just the standard tools and some tomfoolery.

: (btw: I would *never* write the 'global_assigned_ids' table -- imagine the
: perf hit!!  It was just a gedankenkludge.)

--
-Ankur Gupta