CODEWITHSUNDEEP
oracle-databaseprimary-keyguid
cmdematos
cmdematos

Reputation: 913

Should I use Oracle's sys_guid() to generate guids?

I have some inherited code that calls SELECT SYS_GUID() FROM DUAL each time an entity is created. This means that for each insertion there are two calls to Oracle, one to get the Guid, and another to insert the data.

I suppose that there may be a good reason for this, for example - Oracle's Guids may be optimized for high-volume insertions by being sequential and thus they maybe are trying to avoid excessive index tree re-balancing.

Is there a reason to use SYS_GUID as opposed to building your own Guid on the client?

Upvotes: 6

Views: 22821

Answers (4)

Smart003
Smart003

Reputation: 1119

SYS_GUID can be used as a default value for a primary key column, which is often more convenient than using a sequence, but note that the values will be more or less random and not sequential. On the plus side, that may reduce contention for hot blocks, but on the minus side your index inserts will be all over the place as well. We generally recommend against this practice.

for reference click here

Upvotes: 3

cmdematos
cmdematos

Reputation: 913

I have found no reason to generate a Guid from Oracle. The round trip between Oracle and the client for every Guid is likely slower than the occasional index rebalancing that occurs is random value inserts.

Upvotes: 1

tbone
tbone

Reputation: 15473

Why roll your own if you already have it provided to you. Also, you don't need to grab it first and then insert, you can just insert:

create table my_tab
(
val1 raw(16),
val2 varchar2(100)
);

insert into my_tab(val1, val2) values (sys_guid(), 'Some data');
commit;

You can also use it as a default value for a primary key:

drop table my_tab;
create table my_tab
(
val1 raw(16) default sys_guid(),
val2 varchar2(100),
primary key(val1)
);

Here there's no need for setting up a before insert trigger to use a sequence (or in most cases even caring about val1 or how its populated in the code).

More maintenance for sequences also. Not to mention the portability issues when moving data between systems.

But, sequences are more human friendly imo (looking at and using a number is better than a 32 hex version of a raw value, by far). There may be other benefits to sequences, I haven't done any extensive comparisons, you may wish to run some performance tests first.

Upvotes: 8

anon
anon

Reputation:

If your concern is two database calls, you should be able to call SYS_GUID() within your INSERT statement. You could even use a RETURNING clause to get the value that Oracle generated, so that you have it in your application for further use.

Upvotes: 4

Related Questions