Should I use Oracle sys_guid () to generate the commands?

Question

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

I believe there might be a good reason for this: for example, Oracle guides can be optimized to insert large volumes by being consistent, and therefore they may be trying to avoid over-balancing the index tree.

Is there a reason to use SYS_GUID as opposed to creating my own Guid on the client?

Answer 1

I did not find a reason to generate guidance from Oracle. The journey between Oracle and the client for each Guide is probably slower than the random rebalancing of the index that occurs at random values.

Answer 2

Why roll back if you already have this. In addition, you do not need to grab it first and then paste it, you can just paste:

 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 the default value for the primary key:

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

There is no need to configure a before the insert trigger to use the sequence (or in most cases even taking care of val1 or how it is populated in the code).

In addition, maintenance for sequences is supported. Not to mention portability issues when moving data between systems.

But, sequences are more human-friendly imo (viewing and using a number is better than the 32-bit hexadecimal version of the raw value, of course). There may be other advantages to the sequences, I have not made any extensive comparisons, you can run some performance tests first.

Answer 3

If your problem is with two calls to the database, you can call SYS_GUID() in your INSERT . You can even use the RETURNING clause to get the value generated by Oracle so that it can be used in your application for future reference.

Answer 4

SYS_GUID can be used as the default value for the primary key column, which is often more convenient than using a sequence, but note that the values ​​will be more or less random rather than sequential. On plus side, which can reduce competition for hot blocks, but on the minus side, your index inserts will be everywhere. We usually recommend against this practice.

for reference click here