Oracle 26ai and UUIDs - Playing with uuid-v7

TL;DR: The UUID capability of Oracle 26ai prompted me to play with some UUID data. It also gave me ideas for some PL/SQL code. 

But first: Three Caveats:

1) Do Not use this your Production systems (unless you Really Know what you are doing, and even then...).

2) A lot of this blogpost is trickery, or plain Bad-Practice: You Should Not put relevant Information in a technical Key. And do not depend on info that might be available in the key.

3) The Jury is still Out whether UUIDs, in an Oracle RDBMS, really are better than plain old, smaller, readable, Sequences or (Integer-) ID-colunms.

The reason I still coded this, and wrote this text, is that I got inspired (got jealous?) from the python-module for UUID (link) and from some other systems. I tried to copy some of its capabilities. Additionally, Maybe some ppl out there can benefit from some of this code. And lastly, I wouldnt mind to have some discussion on the use of UUIDs.

Image: UUID, clock and universe, generated by some AI tool (it is called Oracle 26ai, Twenty-Six-eeh-aai, after all...)

Background: UUIDs are coming, like it or not..

The use of UUIDs ... Will Just Happen. 

In practice, I expect a lot of new (distributed) system will be designed to generate and process UUIDs. For "distributed systems" those keys are a necessity. Hence your data will contain UUIDs soon.

The jury is out whether UUIDs (stored as RAW, Varchar, or otherwise) are All that useful or efficient in an Oracle RDBMS.  But Given that their use is growing, Expect them to invade your RDBMS soon (if not already).

Allow me a few comments, personal opinions...

Opinion nr 1: In a lot of cases, plain-old Integer-IDs, when used sensibly, may still be usable, and perform even better... YMMV.

Opinion nr 2: For UUID, V4 and V7 probably equally useful, and equally performant, unless you have some Extreme Use Case (or when you plan to use the hidden time-info in a UUID-V7...).

Since Oracle introduced the SYS_GUID (somewhere around 2013?), I have not seen a lot of usage of that function. Possibly because most Oracle systems are much older ad will stick to Sequences. 

Add to that: Experienced Oracle folks (designers, dbas, gurus..) have learned to work around the (perceived!) downsides of sequences. 

Notably:

1. The correct Caching of Sequences alleviates a lot of contention.

2. Partitioning (for time) is Conceptually Better on a true Date- or Timestamp- column than on (tweaked) UUID-V7 values.

But enough Caveats and Opinions now.

Let's Tweak Some... Start with Version.

So, after all these Cautious words, I still could not get the documentation  of the python module (link) out of my head, and decided to do some coding anyway. From the functionality included in the python module, I started with the Easy Ones...

The easiest thing to extract from a UUID is the visible-properties: Version. A function to do that would look like this:

Easy to define, but there are some items to "catch" around the values for MIN/MAX, and how to trap invalid input in general. After a bit of coding and testing, output was generally OK. Result is intended to always be an Integer (number), or NULL, if null is given as input. 

Notice the results for min, max and the negative values that signal "invalid". A  SYS_GUID() will result in a return of -2. A lot more sophistication is possible here, but since I dont have a pressing need or urgent use-case, I didnt go deeper, yet.

Note: a few days after writing, I considered the NIL-UUID should have version 0 rather than the negative -255 ??

One of the Use-Cases for UUID_VERSION could be to check or to report on "incoming data" when ingesting from other systems or sources.

And speaking of Ingesting data....

Conversions from/to Varchar (lowercase and hyphens...)

For use with ingest- and display software, it will be good to have conversions to/from Varchar2. Trivial, but useful. Hence..

 

UUID_TO_VC2 ( RAW) -=> lowercase 8-4-4-4-12. This is covered mostly in the function FMT_UUID ( RAW) already described in the previous blog-post (link). Hence a "wrapper" will do for now. Others (or myself) can improve on it later if needed.

VC2_TO_UUID ( vc2, upper/lower, hyphens, dots... ) -> RAW(16). This will come in useful when ingesting data from text-files or other sources that deliver the UUID as a string.

Those two "converters" are fairly trivial functions, but these will notably allow easy conversion of ingested data. And especially for the "to_uuid", I want an error Raised when input is dubious or invalid. I might put a little more Effort into that later. For now using the same dataset as above:

Currently, the only check is on "length" and valid-hex. Hyphens, if any, will simply be removed from the string, and then it will go HEXTORAW. Any non-HEX will result in Error-Raised, any wrong length will also result in Raise of error...

At this moment, the errors are rather un-elegant. But I would prefer to throw an error than to ingest "corrupt" data. There is room for improvement here, such as removing "spaces" or other separators like dots, underscores etc. Maybe Later.

Time from a UUID7 (which you Should Never Use...!)

Given a RAW UUID-V7, I wanted to extract the Timestamp, either as epoch-value, as oracle-DATE or TIMESTAMP. That should not be too difficult.

These time-extractions can be used to sneakily select ranges with given date-time boundaries. They can also be used to determine the "create-time" of a given UUID-V7 (which may give an indication of the origin, or the time when a certain record was created, should you meed that ...)

It starts with a function to extract the epoch:

Once we can extract the Epoch from a UUID-V7, we can build on that. From the epoch (with approx 3 decimals, hence milliseconds) we can convert to a Timestamp or to a Date. Hence two more functions:

UUID7_TO_TIMESTAMP  ( UUID7 RAW ) => return a Timestamp (without tz)

UUID7_TO_DATE ( UUID7 RAW ) => return a Date (precision on whole seconds)

There is a bonus function included, one that I needed anyway:

EPOCH_TO_TIMESTAMP ( TS NUMBER) => return a Timestamp (without tz).

Demo (link to file at bottom): Select some values of UUID7, including the the NIL and the the max-Epoch, and show the resulting Timestamps and Date-values:

There we are. And if anyone needs it: extracting the time-values from UUID-V1 and -V6 should not be too difficult. But I didnt have the need yet, and I was too lazy...

Also notice the max-epoch value resulting from UUID-MAX. Remember that number and remember that Date...

And to Abuse the time-info....

Now that we have this information, we can do something naughty, like counting records per Hour, based on just the UUID...

 We see an example of a count of record per day-hr, based on the information hidden in an ID column with keys that conform UUID-V7.

And as you can verify, no DATE or Timestamp column was used in this demo...

I can not repeat enough how this is ABUSE of the UUID(V7)... But it might happen.

Another way you can use those implicit time-values for is to check on UUID7 keys: if the resulting timestamp is before, say, 2020, the data is probably "generated" and not "genuine" because the Standard for UUID-V7 was only designed after 2020. Just saying.

Summary and closing remarks...

Regardless of the fact that UUIDs were Firstly meant to be Unique + Random, there can be some "information" hidden in them. Python (and other systems) are able to expose that information. Other systems, notably the Python module for UUID, can extracat that information. So I also built a few primitive PL/SQL functions, just to play.

We can those functions to extract version and date/time values from UUIDs as s "forensics tool". And we can do some conversion (and formatting) between Varchar2 and Raw for use in interfaces.

I had also thought about constructing UUID-NIL and UUID-MAX, but those are so trivial that I will leave that to the next hobby-programmer.

For Practical use? ...  I would recommend to be Very Careful to Use those in Prod. A true Key-field should Never be abused for "information". Period.

Question for Oracle: Will they succumb to temptation to include extractor-functions|? Or will they stand on the opinion that Key Data should be Meaningless ? 

Note: The links to the UUID-documentation is Evolving as we Write. For example, this page on function IS_UUID appeared recently in my searches... Good Sign.

Oh, and the elephant in the room: Should I demo the use of UUID-V7 for use in partitioning or other constructs with time-restricted data ?  We all know it can be done. But doing it will set a Bad Example. 

My aversion to use Key-values for anything other than Key-Reference is still strong...

-- -- -- -- -- -- End of this blogpost, for now -- -- -- -- -- --

Appendix A: links to scripts

mk_uuid7.sql :  Original from previous blogpost, with some corrections.

mk_uuid7_p2.sql : The additional functions.

-- -- -- -- -- -- End of this blogpost, For Real -- -- -- -- -- --

Oracle 26ai - UUID v4 available, and looking at v7, Interesting!

TL;DR: in Oracle 26ai, there is the new function UUID(), which implements the UUID-V4. Someone also mentioned UUID-V7. I started experimenting with the Possibilities.

And of course I expect (hope) Oracle implements other versions of UUID quite soon...

(For the purists on Standards and Database-IDs, keys: Several Caveats included. Dont do this in Production unless You Know What You are Doing...)

Image: some Universe with the UUID.

Background: UUID are everywhere (but I avoided them)

So far, I have not encountered UUIDs much in Oracle. Some other databases use UUID, and to me it was mostly a funny-looking random-string. I was aware of the Benefits of UUIDs: Uniqueness + Randomness, even when generated at different place and time. And in Oracle there was the SYS_GUID() function, should we need it. But I dont think I ever used it.

That May Change: Because in Oracle 26ai, there is a function to generate UUID-V4 keys. 

Here is the link to the Standard Paper (which I didnt Study in Detail myself...)

Jonathan Lewis experimented some, he also dug into the error-messages, and pointed out that Oracle will probably also implements the versions 1, 2, 3, 7, and 8 (link to forum) 

In the Database-world, the growing consensus seems to be that UUID-V7 is Better. Notably in this article is an interesting example of someone who tried to benchmark, and concluded that "in his case" the UUID-V7 was better in a number of scenarios. 

We also had at least 1 Oracle-DBA who was Actively Asking for UUID-V7. And as Jonathan Lewis showed, the indications are that this will be implemented at some point in the (near) future.

Let's write some code...

DBA's being Data-Geeks. Shortly after the intro of UUID-V4 in Oracle came the discussion whether UUID-V4 was good, was usable, or was just plain bad for use (as a key/reference/pointer) in an Oracle RDBMS.

Geeks have to Geek, hence... Jasmin Fluri wrote a PL/SQL function to generate UUID-V7 keys and blogged her findings here (link). I could not resist, and took that code, tweaked it a bit, and started experimenting as well. As a result, I ended up with a few functions that I can share, and that I might use in further experiments. 

Link(s) to script(s) are at bottom of blog, in case someone wants to mess with it.

Raw, but (not very) Readable...

The Basis if the UUIDs is a 128-bit number, and this wiki-page on UUIDs is quite extensive.

Given the definition of the standard, and the nature of the documentation found so far, the consensus between myself and a small group was: Store this as RAW(16) for the moment (we might re-consider that later, and of course ymmv...).

With a copy of the code from Jasmin, I was able quicly able to create UUIDs of V7.

For the complete source: link at bottom.

I was now able to create UUID-V7 and experiment with them.

From working with "the other database", I was used to see UUIDs in a certain format. And I sometimes had data-sets delivered to me with (quoted) GUID-keys in them. They are generally displayed as 8-4-4-4-12, as per Standard. Both the Standard and the Wiki-page also states that the Lowercase is preferred. 

At this point I regretted that Oracle did not have (yet) a data-type for UUID. In the other database, I can define a column as a UUID, and get the display-formatting automatically on selecting the column.

Oracle provides the function RAW_TO_UUID, but at this moment, Oct 2025, with v23.26ai, that only works on UUID-V4 keys.

So out of habit, I wrote a formatting-function to facilitate display of generic UUIDs. The result was :

As we can see, the function is able too format most forms of UUID, including the old SYS-GUID() from Oracle v12.x. Can you spot the V4 and V7 UUIDs?

Time-ordered keys, hence I want Boundaries, Ranges.

The UUID-V7 is specifically time-ordered. That means that if you use them in an ORDER-BY, that the values are sorted by "created_date".

Thus, if I wanted to select all the records with UUID-V7 keys generated between T1 and T2, I needed to be able to specify T1 and T2, Precisely, and in a RAW, UUID usable type.

This called for a function that would generate the UUID7 for a Given Timestamp. The result was :

By specifying a timestamp, I obtain the corresponding UUID. And I placed some irrelevant (?), but informative data in the "random bytes" after the last two groups. 

Caveat - Note: I ignored the "variant" information from the Standard-spec. Something to think about later.

The results, output, can look like this :

The first two lines in the result are the UUID generated with explicitly-specified SYSDATE and SYSTIMESTAMP. And the time is "readably" stored in the last two groups of 4 + 12 bytes. You can recognise the ID generated from SYSDATE in the first record from the last two bytes: Because the DATE is only accurate to the Seconds, the Centi-Seconds, are "00".

You can experiment for yourself with the peculiar difference between :

Select uuid7_ts ( systimestamp) from dual connect by level < 3;

Select uuid7_ts ( )             from dual connect by level < 3;

There might be discussion on the implementation-possibilties there... Later.

Main point is: I can now filter the UUIDs generated before, after or between timestamps. 

Caveat! ... This is Not Best Practice (abuse of "information" in key-fields).. But it can bed Very Useful when analyzing data per hour / day /  week.

Closing Summary (hmmm, not yet closing...).

It looks like we can now generate UUID-V7 values. And we can even generate them with "given timestamps". 

We have the following functions:

- FMT_UUID ( UUID ) : return a varchar2 with uuid-format: 8-4-4-4-12 for "display" usage. For formatting the other UUIDs, since Oracle choose to accept only UUID-V4 for their RAW_TO_UUID (), at this moment (Oct 2025).

- UUID7 () : return a RAW(16) with the UUID of V7

- UUID7_TS ( Some_TS ) : return a RAW(16) with a UUID-V7, but with the time-part set to the given parameter. 

I look forward to playing with those ...

Because there is More...

Now, Once we have the ability to generate "precisely timed values", we can use those to allow partitioning on Time-ranges. Something for another session. 

Then there is the question: Do I want to Know the Time ? The UUIDs of type V1, V6 and V7 contain timestamp-bits. I can potentially extract the time-of-generation from those. Would this be useful? 

For a future versions, I would also consider creating a package to bundle all my UUID-related functions. A package will also allow "overloading" the two functions UUID7() and UUID7_TS() to a single "name". Something to think about.

Give me some time to think and experiment...

Or.. You can experiment Yourself. 

The best learning is from the things You Do Yourself - so Go tweak this code.

Enjoy !

-- -- -- -- -- -- End of blog text, for now -- -- -- -- -- --

Appendix A : links to Scripts..

mk_uuid7.sql : create the functions, and some test code to verify.

demo_uuid_fmt : demo the formatting.

-- -- -- -- -- -- end of this blog-episode, for real -- -- -- -- -- --