Re: GUID as primary key

Billy schrieb am 20.06.2005 in
<1119269670.182088.34550_at_g47g2000cwa.googlegroups.com>:

> Andreas Mosmann wrote:

>> Hi,
>> 
>> <Oracle 9.2i>
>> I got 2 questions:
>> 
>> Is there a package (or something else) where I can get a GUID from the
>> oracle system? (something like SELECT DBMS_GUID.GenerateGUID FROM DUAL)

> SQL> select sys_guid() from dual;

> SYS_GUID()
> --------------------------------
> F9F937F446ADEFD2E03001C450532277

Many Thanks

>> We want to use GUID for all Rows in the Database. We can do this by the
>> application, but sometimes (Triggers etc.) it would be useful to
>> generate a GUID in the database directly.

> Why not use a sequence instead? Sequences are tailor made for scaling
> with concurrent usage. I doubt that SYS_GUID is.
There are some reasons for not using sequences - a sequence looses the effect if only 1 row is inserted with a number higher than AnySequenceName.CurrVal, so you have to fix this in an application. Either you write an insert- Trigger for that table, that (in such cases) kills the sequence and builds a new on with startvalue=inserted value or you have to make the application stable for the case, the used key doesn't work
- I do not know a possibility to generate keys that are composed by some other values (f.e. an object-number consists of 4digits for the actual department when it is created and 6 digits counting 0..999999). To do this I had to use a stored procedure, that evaluates the key - our special case:
1 Database but Several Departments sometimes without connection So we will solve this problem by doing the following: * When connection exists the historical data are copied to the small dbs (XML or something like that) and the moving date (?) are copied to the database. Because of every department only inserts his own data there is no trouble with data but with a simple sequence (remember, sometimes there is no connection). If we use GUID we will (hopefully) fix this problem.

>> You can give a column in a table a default value. How can I give oracle
>> a function instead of a plain value so that this function is called
>> everytime a new row is generated?

> Why not simply code it in the INSERT statement? Is it honestly such a
> good idea to hide the sequence number from the INSERT? What does it buy
> you? What does it cost you in the long run ito maintenance when a
> developer touches that code?

See the paragraphes above: If I could f.i. give sys_guid() or MyFunction as a Default value the application development is much easier. You could use this value directly for Master-Detail-Relations if you want, you could use sysdate ore som functions depending on sysdate by default ... AFAIK this feature is in more than 1 Database, why shouldn't it in Oracle?

> I've heard some good arguments for why not obfuscate the use of
> sequences with the counter arguments not being that strong.
Everytime you have to consider anything - hope our decision was right.

Thanks a lot
Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de