The Hibernate team has already implemented this solution, which I utilized
in the test. I mentioned previously that despite this improvement, we still
observe a slight impact due to the client needing to fetch the next chunk
from the database. This aspect makes sequences slightly slower compared to
UUIDs. The Hibernate team uses a variable to control the size of the chunk,
with the default being 50. Surprisingly, I found through past tests that 50
is the optimal value. Whether using 50 or 1 million, I didn't notice any
significant improvement.
Although I don't have an explanation for this phenomenon, it doesn't matter
much because the performance with a chunk size of 50 was acceptable.
I conducted this investigation because no developer or architect in the
organization I work for had an explanation for why UUIDs were chosen over
other options. Thus, I had to discover the reasons myself.
Furthermore, I conducted numerous tests aimed at enhancing batch inserts in
parallel using Java/Hibernate with sequences, and I achieved very
satisfactory results. I was simply skeptical about why nowadays I see the
widespread use of UUIDs instead of sequences, especially among individuals
with less experience in databases.
Ahmed
-----Original-Nachricht-----
Betreff: RE: UUID vs. Sequential ID as Primary
Datum: 2024-04-13T15:31:55+0200
Von: "Mark W. Farnham" <mwf@xxxxxxxx>
An: "mwf@xxxxxxxx" <mwf@xxxxxxxx>, "ahmed.fikri@xxxxxxxxxxx"
<ahmed.fikri@xxxxxxxxxxx>, "'list, oracle'" <oracle-l@xxxxxxxxxxxxx>
Fixing typo:
For example, if you use 1000 clients in parallel and a sequence chunk size
of 1000, that would be only one sequence request per 1 million records.
Should be:
For example, if you use 1000 clients in parallel and a sequence chunk size
of 1000, that would be only one thousand sequence requests per 1 million
records.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] ;
On Behalf Of Mark W. Farnham
Sent: Saturday, April 13, 2024 9:11 AM
To: ahmed.fikri@xxxxxxxxxxx; 'list, oracle'
Subject: RE: UUID vs. Sequential ID as Primary
Since you need only uniqueness and not monotonically increasing values, you
can allocate reasonable chunks from a very limited number of requests to
the database.
For example, if you use 1000 clients in parallel and a sequence chunk size
of 1000, that would be only one sequence request per 1 million records.
This should not be difficult with Java and hibernate for n clients to use
whatever chunk size you deem. In Oracle sequences you can use increment by
establish the size of a chunk, but there is a slight chance that alter
sequence would better fit your operational model.
It is still possible that a client uuid would be the better choice in a
particular situation, but it is unwise to make that decision because you’re
buying a loaf a bread by riding a bicycle roundtrip to the store and
purchasing one slice at a time. (Thanks Cary Millsap for that memorable
fable.)
I mentioned incrementing the sequence to get a cache of sequence values for
a slug of rows for each client in my earlier reply, but apparently I was
not clear about what I meant.
Regardless, good luck,
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[<mailto:oracle-l-bounce@xxxxxxxxxxxxx> ] On Behalf Of
ahmed.fikri@xxxxxxxxxxx <mailto:ahmed.fikri@xxxxxxxxxxx>
Sent: Saturday, April 13, 2024 4:37 AM
To: list, oracle
Subject: AW: UUID vs. Sequential ID as Primary
Hi,
For those who need to decide which primary key type to choose, I conducted
a test myself, initially on PostgreSQL, and I believe the results would be
similar for Oracle.
The test involved creating and saving 1 million records using Java (with
Hibernate) as the client:
strategy
Field Type
entities creation elapsed time (ms)
saving entities
elapsed Time (ms)
Table Size
(mb)
Index Size
(mb)
UUID
String
7768
181184
80
73
UUID
UUID
7763
172367
57
37
Sequence
Long
10036
163351
49
21
UUIDs consist of two Longs, which means they require double the space
compared to a single Long. However, despite this overhead, we observed that
the creation of entities using UUIDs at the client-side is faster compared
to using sequences. This is due to the fact that UUIDs allow for
client-side ID generation, reducing the need for round trips to the
database during entity creation (Despite efforts to minimize its impact for
sequences, we still observe its effect).
On the other hand, saving entities with Long IDs takes less time than
UUIDs. This is because Longs require less storage space and hence result in
quicker database operations.
In my case, working with microservices and distributed systems, the
preferred primary key type turned out to be UUID
Regards
Ahmed
-----Original-Nachricht-----
Betreff: Re: UUID vs. Sequential ID as Primary
Datum: 2024-04-11T21:36:16+0200
Von: "Jonathan Lewis" <jlewisoracle@xxxxxxxxx
<mailto:jlewisoracle@xxxxxxxxx> >
An: "list, oracle" <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
