Re: UUID vs. Sequential ID as Primary

On Thu, 2024-04-11 at 06:37 +0200, ahmed.fikri_at_t-online.de wrote:
> Hi there,
>  
> Once more, a question about fundamentals:
>  
> In several Hibernate projects, I've observed developers leaning towards
> using UUIDs as primary keys instead of numerical values. This preference
> likely stems from the avoidance of sequences for numerical primary keys,
> which necessitate round trips to the database after each insertion.
> Additionally, there's a concern about potential contention with
> sequences, and some developers may prefer to avoid predictability in the
> next generated value. Personally, I remain skeptical about the widespread
> use of UUIDs due to their larger storage footprint (both in tables and
> indexes) compared to numerical IDs. Numeric IDs also offer benefits in
> issue analysis. However, it's worth noting that Hibernate employs
> algorithms to minimize round trips to the database, effectively reducing
> their impact. Could you please share your experience and preference? What
> choice would you make?
>  
> Regards
> Ahmed
> 

This is a very interesting question. There are several differences between sequences and SYS_GUID():

• SYS_GUID doesn't cache entries. It will be necessary to make a call for every entry.
• SYS_GUID is not ordered, it doesn't guarantee order, it only guarantees uniqueness.  Sequences with their caches and adaptability (see "identity type") are, in my opinion, still better than SYS_GUID(). And yes, your remark about the storage required to store the UUIDs is correct as well. My vote goes to sequences.

-- 
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com


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