UUID v7 in Oracle Database

I wrote about UUIDs in the Oracle database two months back in this post , but it turns out that UUID v4 is so… 2005 .

You probably need UUID v7

Head over to How UUIDv7 makes your (database) life easier to understand why you want v7.

As mentioned in the article there is no native support for v7 in Oracle, neither a function to generate it or a proper datatype for UUIDs like PostgreSQL has. But the article shows an implementation of v7 generation in a PL/SQL function called generate_uuid_v7 that I used in some testing.

UUID from JavaScript

Now that we have a solution for this for any version of Oracle after version 6 when PL/SQL was introduced we don’t need to look further. But this is an example where JavaScript in the database (since version 21c) is useful. JavaScript libraries for generating all kinds of UUIDs can be found rather easily, and with a few lines of code they can be called from PL/SQL.

In this example I am using a library uuidv7 found at npmjs.org:

npm install uuidv7

Save this into a file called e.g. uuidv7.js:

import { uuidv7, uuidv7obj } from "uuidv7";

export function uuid_v7() {
  return uuidv7();
}

export function uuid_v7_raw() {
  return uuidv7obj().bytes ;
}

The following command builds a bundle that we’ll load into Oracle:

npx esbuild uuidv7.js --bundle --outfile=uuidv7-bundle.js --format=esm

SQLcl has a command to load MLE bundles into Oracle:

mle create-module -filename  uuidv7-bundle.js -module-name uuidv7 -language javascript -replace

The PL/SQL call specifications look like this:

CREATE OR REPLACE FUNCTION uuid_v7 RETURN VARCHAR2
AS MLE MODULE uuidv7 SIGNATURE 'uuid_v7';
/

CREATE OR REPLACE FUNCTION uuid_v7_raw RETURN RAW
AS MLE MODULE uuidv7 SIGNATURE 'uuid_v7_raw';
/

Compared to the ready made PL/SQL function generate_uuid_v7 this have more steps, but if you have an implementation in JavaScript somewhere, this is a reasonable way to have utility functions available in the database. Just have a look at validator.js and imagine how to implement all those validation functions in PL/SQL (if you can find a PL/SQL developer).

Performance

Back to the UUIDs. With native support for v4 in Oracle we expect it to be much faster than calling a PL/SQL function and perhaps even worse with a function based on MLE JavaScript. But does it matter?

Let us do a simple test. First create a table with 1 million rows using the new builtin UUID function:

create table uuid_v4 as
select uuid() uuid, dbms_random.string('a',42) foo
from dual
connect by level <= 1e6;

On my Mac mini with M1 CPU and Oracle 23.26 FREE edition this takes 21.5 seconds¹.

Same test but with the PL/SQL function generate_uuid_v7() from the referenced article:

create table uuid_v7_plsql as
select generate_uuid_v7() uuid, dbms_random.string('a',42) foo
from dual
connect by level <= 1e6;

This one takes 30.3 seconds.

And finally the JavaScript version uuid_v7_raw():

create table uuid_v7_js as
select uuid_v7_raw() uuid, dbms_random.string('a',42) foo
from dual
connect by level <= 1e6;

This one take 38.20 seconds

The JavaScript version takes 77% more than the new uuid() function. But does it matter? It is less than 17 microseconds extra pr row. I doubt that the end-user will notice, or that this has any significance unless you generate a whole lot of UUIDs.

I did some investigating around the indexes on the UUID column and compared it with an index on a sequence based key. The clustering factor increased a lot with the UUIDs, but this may be different in a production system, perhaps on RAC where each node has a cache for the sequence. Also the number of leaf blocks in the indexes on UUIDs increased substantial.

Earlier I looked into some testing using Swingbench; my plan was to compare performance with sequence based IDs vs UUIDs. With Swingbench you typically generate data in a schema before you run the test. Somewhere in the code there is an initial functional test where a select statement assumes the existence of row in a specific table that has an ID=1, which is safe because the data generation is deterministic since sequence is always created with a start value of 1. But you can’t make such assumptions with UUIDs because the UUID will not be deterministic.