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.
