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] On ;
Behalf Of 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>
An: "list, oracle" <oracle-l@xxxxxxxxxxxxx>
When considering the overheads and side effects of sequences it's worth
remembering that in 12c Oracle introduced the "scale" and "extend" options to
prepend the instance id and session id to the generated value so that
contention between instances and between sessions on the same instance would be
minimised.
It's also worth remembering that 19c introduced an automatic resizing strategy
for the sequence cache (which introduced problems for some people, especially
in RAC) to work around the contention at sites that didn't set a sensible cache
size for their sequences. (See comments on this note: Sequence Accelerator |
Oracle Scratchpad (wordpress.com)
<https://jonathanlewis.wordpress.com/2021/08/06/sequence-accelerator/> )
Regards
Jonathan Lewis
On Thu, 11 Apr 2024 at 18:53, ahmed.fikri@xxxxxxxxxxx <ahmed.fikri@xxxxxxxxxxx>
wrote:
Thank you all for your insightful responses. I share the concern about
mistakenly assuming uniqueness, as it could have serious consequences down the
line.
As for Peter's suggestion, the challenge lies in the fact that the client
doesn't handle the insertion process; it simply needs to generate a unique ID
for an entity without directly "interacting" with the database. This can be
achieved if the client understands how IDs are generated, such as being aware
of a sequence on the database side and can access that sequence. However, when
using identities, there's a significant hurdle because the client lacks access
to the internally generated sequence, even if one is utilized server-side.
Consequently using IDENTITY leads to poor performance as just creating a row at
client side require round trip to the database (The client determines when and
whether to insert eventual rows into the database, so it creates a sort of
local cache that should, at a certain point, mirror the database). The system
only functions smoothly if the client can interact with the sequence directly.
Personally, I lean towards using sequences, but I hesitate to recommend them to
others without being able to precisely justify why. Perhaps there's a benefit
to using UUIDs that I'm not yet aware of.
