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
<mailto:ahmed.fikri@xxxxxxxxxxx> <ahmed.fikri@xxxxxxxxxxx
<mailto: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.
