RE: Re: UUID vs. Sequential ID as Primary

The expert answer is (always) that it depends.  

Whence the UUID? (Mladen, as was reasonable on oracle-l presumed sys_guid()), but you may be using something local to the client.  

Is there a constraint on the database ensuring the UUID is in fact unique?  

If you have a UUID generator you trust on the client that might be a slight advantage over a good sequence+ range for your planned cache of sequence values with one fetch for the client preparing a slug of rows to insert.  

Sequence is very likely to be more compact and if you are tracking integrity of insert slugs or auditing after the fact, contiguous sequence values can be useful in puking out the transaction details.  

If you’re reserving a range of sequence value per inserting client, you tend to minimize the likelihood of a hot spot on any indexes including the sequence since the likely numbers of inserters into the same index leaf are 1 and 2 (if clients with adjacent reserved ranges insert at the same time they might easily hit the same leaf, and it could be more with small reserved ranges and small batches).  

Time plus sequence can often be useful, but that is not generic and only applies when ordered “born on” date for rows inserted is useful (very often that is true for business data, less often so for spectrographic analysis and maps).  

Others can probably wax on for additional case analysis parameters of which might be a better prediction of better behavior without an actual test.  

But it depends. I tend to use sequence (or time plus sequence) unless something about an actual case in hand screams “a sequence will be a problem.”  

A test of meaningful scale is the only way to be sure for a given case. When computers were a lot slower actual tests of doing things two or more different ways took place. Now we mostly look for a forecast and re-tool in the event the horse we have chosen stumbles.  

Good luck and let me know if you find and expert (especially one who claims the answer is invariant and not “it depends.”)  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ahmed.fikri_at_t-online.de Sent: Thursday, April 11, 2024 10:35 AM
To: peter.m.gram_at_gmail.com
Cc: list, oracle
Subject: AW: Re: UUID vs. Sequential ID as Primary  

Thanks for the hint, you’re right, one can use that. The problem is that when working with hibernate (or any other client lib) one doesn’t want to go to the database to fetch the IDs when creating an entry on the client side. While one does want to create the ID uniquely, it should be done with minimum visiting the database (using sequences has now been optimally solved e.g. hilo algorithm, not just for Oracle, but almost all other databases).

I’m interested in our experts have to say about this: is the trend of using UUID legitimate, or is using numbers better?

Gesendet mit der Telekom Mail App <http://www.t-online.de/service/redir/emailmobilapp_ios_smartphone_footerlink.htm>

-----Original-Nachricht-----

Von: Peter Gram <peter.m.gram_at_gmail.com> Betreff: Re: UUID vs. Sequential ID as Primary Datum: 11.04.2024, 16:10 Uhr
An: <ahmed.fikri_at_t-online.de>
CC: list, oracle <oracle-l_at_freelists.org>

Hi  

If you use the returning clause on the first statement you don’t get a extra round trip to the database to get the sequence.

Med venlig hilsen

Peter Gram
Sæbyholmsvej 18

2500 Valby

Mobile: (+45) 5374 7107

Email: peter.m.gram_at_gmail.com      

On Wed, 10 Apr 2024 at 23.38, ahmed.fikri_at_t-online.de < 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



--

http://www.freelists.org/webpage/oracle-l Received on Thu Apr 11 2024 - 17:57:55 CEST