AW: Re: UUID vs. Sequential ID as Primary
- From: ahmed.fikri@xxxxxxxxxxx
- To: list oracle <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Apr 2024 09:02:33 +0200 (CEST)
Thanks Jonathan!
Gesendet mit der Telekom Mail App
<http://www.t-online.de/service/redir/emailmobilapp_ios_smartphone_footerlink.htm>
-----Original-Nachricht-----
Von: Jonathan Lewis <jlewisoracle@xxxxxxxxx <mailto:jlewisoracle@xxxxxxxxx>
Betreff: Re: UUID vs. Sequential ID as Primary
Datum: 11.04.2024, 21:35 Uhr
An: list, oracle <oracle-l@xxxxxxxxxxxxx <
mailto: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.
Other related posts:
