AW: Re: UUID vs. Sequential ID as Primary

 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_at_gmail.com <mailto:jlewisoracle_at_gmail.com>
>

Betreff: Re: UUID vs. Sequential ID as Primary Datum: 11.04.2024, 21:35 Uhr
An: list, oracle <oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> >

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_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> < ahmed.fikri_at_t-online.de <mailto:ahmed.fikri_at_t-online.de> > 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.        

--
http://www.freelists.org/webpage/oracle-l