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 -- -- -- -- -- --
No comments:
Post a Comment