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

Question

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?

Answer 1

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.

Answer 2

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.