Learning is not a spectator sport

I wrote a post a few years back (well, actually it was 2018!) but I’ll paraphrase briefly.

There are two common schools of thought when it comes to automatic generation of values for a surrogate key in the Oracle database. The first one is using a SEQUENCE, referencing it either directly in your table definition/SQL, or taking advantage of the IDENTITY clause that’s been available since 12c which is underpinned by a hidden sequence in the data dictionary.

The other one is to generate a GUID and the way most of us have done that in the past is by using the SYS_GUID function in the Oracle database. My personal preference has always been a sequence, especially when it comes to things like index density and overall performance, and also because over the years in various versions of Oracle the SYS_GUID function on some platforms has had some dramas in terms of performance. However, the drawback of a sequence is that while that surrogate key value is not typically going to appear on your application screens, it might appear in the URL or in various parameters that get passed around in REST payloads, etc. In that instance, an ascending sequence opens up the opportunity of it being relatively easily to guess by someone with malicious intent.

A GUID seemingly resolves this because we get this random string of bytes every time we call it. As well as it being more or less unique across any database we would ever choose to use it on. But that brings up my other concern with the SYS_GUID function – namely, it can have different behaviours depending on the platform you’re running on. In particular, if you’re running on the most common platform – Linux.

Before I get to that, let’s just do a simple example where we retrieve the GUID and we’ll see that the function returns a RAW datatype. Our SQL CLI converts that raw to hexadecimal but it is indeed a 16-byte raw which appears as 32 bytes of hex on our SQL output display.

Luckily the highest possible value for such a GUID just squeezes into the maximum available limits of the NUMBER data type in Oracle so a common practice is to convert that GUID to a numeric surrogate key, because people generally prefer dealing with numbers instead of raw hexidecimal.

Calling the same function again – I get a fresh pseudo random number for my next surrogate key. All looks fine

However, what appears to be a random GUID starts to show some interesting patterns when you execute it repeatedly depending on the platform you’re on. For example, here are three consecutive executions of the GUID function, this time returning the raw string as opposed to the converted number. They actually look identical but I’ve highlighted the (single nybble) difference between each iteration.

Notice something interesting? This no longer appears to be the “impossible to guess” random string of bytes that we were led to believe. You do indeed get slightly different patterns when you start running SYS_GUID across different sessions.

However, in an environment where you are reusing sessions in a connection pool, you do run a small but non-zero risk of someone being able to request a GUID legitimately but then use that value to start taking more intelligent guesses at what potential other GUIDs have been requested in that session. In a connection pool environment, those GUIDs may not necessarily have come from your own logical session or even your own application.

For that reason, once you get to Oracle AI Database 26ai, I’d recommend you cease usage of the SYS_GUID function and use the new implementation which is exposed to us as the simply named UUID function.

Like all good universal standards, there is more than one “standard” GUID :-), and as a result there are various types of UUID function.

• V1 – Time-based
• V2 – DCE Security
• V3 – Name-based (MD5)
• V4 – Random
• V5 – Name-based (SHA-1)
• V6 – Reordered Time-based (Draft)
• V7 – Unix Epoch + Random (Draft)

The one that is implemented currently in 26AI is UUID version 4. However (safe harbour), we are hoping to also implement in a future release update UUID version 7 which has the very nice properties of both being a random string in true UUID fashion but those random strings also maintain a chronological sequencing. Hence each UUID will sort slightly higher than the previous one, thus giving a return to the nice index density characteristics of a standard sequence number used as a surrogate key.

My original post talked a lot about the performance cost of obtaining a GUID with SYS_GUID() but all of those bugs have long since been cleaned up. So that outstanding question then becomes: How does the new UUID function compare? We can test that out easily

SQL> set timing on
SQL> select max(sys_guid()) from
  2   ( select 1 connect by level <= 1000),
  3   ( select 1 connect by level <= 1000);
MAX(SYS_GUID())
--------------------------------
584418FCEBC20CE6E0637238A8C0E9B3
Elapsed: 00:00:00.09
SQL> select max(uuid()) from
  2   ( select 1 connect by level <= 1000),
  3   ( select 1 connect by level <= 1000);
MAX(UUID())
--------------------------------
FFFFF37B6496413EA014D2EC440513E7
Elapsed: 00:00:00.17

So it is a little less efficient, but unless you are requiring over (1,000,000 / 0.17) 5million executions per second, I think this will be more than sufficient performance for you 🙂

TL;DR – as part of your upgrade to 26AI, start considering a migration from the SYS_GUID function to the UUID function. Happy upgrading!