sequences

When I started working with databases, one of the first things I learned was to put an ID column on pretty much every table, even if it's never displayed to the user. And I was always a bit miffed that Oracle doesn't provide any sort of automatic identity column support–in SQLServer, if you declare a column to be an identity, it automatically handles everything for you; every time you insert a new row, you get the next value in the sequence. To do the same thing in Oracle, you need to set it up yourself by creating a sequence and a “before insert” trigger. I felt like Oracle was almost encouraging bad behavior by making it harder than necessary to create an identity.

Then I started building packaged applications,some of which are built into APEX 4.2 and the new Oracle Cloud system. And I learned from those who know a lot more than I do that, while an auto-incrementing sequence is effective, it's actually not ideal; if you have data from two tables which you want to meld together (say you're replacing two separate systems with one unified one), you're going to have to use some sort of formula to transform the identity columns of one or the other so that they can live together–you can't have two records with an ID of 1. And there are other issues with sequences (I've posted before on the caching behavior creating gaps, and I've had to force SQLServer to change the start point for its identity columns on multiple occasions, for instance).

Turns out there's a better option (at least for Oracle). Rather than using a sequence, use the sys_guid() function. This creates a globally unique identifier every time you call the function–it's a 16 byte pseudo-random RAW value that's pretty much guaranteed to be unique. (Obviously, eventually you'd run out of values, same as with a sequence; but that's going to take a long time.) Since numbers are much better for ID columns than RAW values, we wrap the call to sys_guid in a to_number call, using the format mask to get the hexidecimal values for each byte. Put that in your “before insert” trigger, and you're done. No sequence necessary:

create or replace trigger [TRIGGER]
    before insert on [TABLE]
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
    ...
end;

A few months ago, a coworker and I were puzzling over the index values in an application I'd built. Specifically, we were astounded to find that our request_id values were over 4,000, when we knew for a fact that there had only been a few hundred requests made so far.

Now, this was not an APEX application, but I've found that APEX, by default, is vulnerable to the same explosive growth in index values. For primary key values, which are not shown to your users (usually), this isn't really a big deal–if you declare the column to be a number without specifying a precision, Oracle guarantees support for 38 digits, so as long as you don't hit 100,000,000,000,000,000,000,000,000,000,000,000,001 you're ok¹. But sometimes you may want the sequence values to be tightly packed; personally, I like a tidy database, so seeing large gaps in my primary keys irks me, even if no one else knows they're there.

So, what causes those gaps? It turns out that the standard “create sequence some_seq;” statement fills in some defaults (well, yeah, that's obvious, but bear with me). Some are what you probably want–start with 1, increment by 1–but one can be problematic. By default, Oracle caches the next twenty values of a sequence when you query its nextval; if you don't use them in that session, they're “lost”–the next nextval will give the twenty-first value. So the fix is simple: when you create a sequence, if it's only used sporadically, specify the nocache attribute.

…which brings us back to APEX. Now, I love APEX, but… When you create a sequence in the SQL Workshop, you can specify the number to cache, and leaving the field blank results in nocache being set appropriately. But. Yes, there's a but. When you're creating a table, if you let APEX generate the sequence for the primary key, it doesn't specify any attributes at all–including nocache. So, if you decide it matters, you have three options: create the sequence yourself before the table, alter the sequence after it's created, or copy the creation script from the final step of the table creation wizard, edit it, and run it manually. Personally, I'd go for one of the first two options, but hey–it's your choice.

¹ 1e+39 only needs one digit of precision, but (1e+39)+1 requires 39. Fun with math! Yay!

How do you identify?

Mind the gap!