Software Development

Sharing an identity space between Oracle databases


I've mentioned before in this space that I'm rather new to Oracle databases. My Oracle experience really only started about 15 months ago. One of the things I thought was weird at first was that Oracle doesn't have an identity type, i.e., nothing you can create as a column that auto-increments itself. It turns out that this quirk does lend itself to some useful little hacks, like being able to share the same identity space easily across multiple databases.

Let's start by looking at the simple case where you have one table in one database. Generally you would start by creating a sequence:

create sequence seq_my_identity_type

Then you'd create a trigger that selects the next new value from this sequence and stores that value as the ID field. The process of selecting the next value causes Oracle to automatically increment the sequence.

In our simple example, let's say you've got a table called id_test, and it has just two fields: a number field called ID and a text field called message:

CREATE TABLE id_test

  ( id NUMBER primary key not null,

  message VARCHAR2(100) )

Then, a trigger to auto-increment that ID field, using the sequence we created earlier, would look something like this:

CREATE OR REPLACE TRIGGER t_id_test_bi

  BEFORE INSERT ON id_test

  REFERENCING NEW AS NEW

  OLD AS OLD

FOR EACH ROW

begin

  if inserting then

    if :new.id is null then

      select seq_my_identity_type.nextval

      into :new.id

      from dual;

    end if;

  end if;

end;

It seems like a lot of work when most other DBMS environments have an auto-increment type you can just assign to the ID field so it gets a unique key whenever anything inserts to the table. But one of the cool aspects to this setup is that now we can use the sequence to ensure unique IDs across multiple tables or the same table in multiple instances of the database.

Let's look at that second scenario in more detail. Say we have two databases: the live production one called content_prod and our QA server called content_qa. I'll assume that in the content_qa database you've created a database link called content_prod so that you can access it from QA.

The sequence, trigger, and table we created earlier exist in content_prod. In QA, we have a copy of the id_test table as well. However, we want to make sure that if any new content is added to the QA table, it can easily get copied into the production version without worrying about collisions between the ID fields in the two tables. One way to ensure that is if QA and production generate their IDs out of the same sequence. Thus, any new content inserted in one will get an ID that's guaranteed unique between the two. So, in QA, instead of creating a separate sequence, we just create a synonym that accesses the sequence in our production database:

create synonym syn_my_identity_type for seq_my_identity_type@content_prod

Then, the version of your trigger in QA would use this synonym to grab the ID field:

CREATE OR REPLACE TRIGGER t_id_test_bi

  BEFORE INSERT ON id_test

  REFERENCING NEW AS NEW

  OLD AS OLD

FOR EACH ROW

begin

  if inserting then

    if :new.id is null then

      select syn_my_identity_type.nextval

      into :new.id

      from dual;

    end if;

  end if;

end;

Now I'm not an Oracle performance expert, but I doubt you'd want to do this in a situation where the sequence gets incremented rapidly in one or both of the databases -- like an event logger or my error-logging system. I'd be worried about the performance impact of having all that contention between databases for the shared sequence.

Where I have used it is in simpler scenarios with much lower frequency of updates. For example, when I have a table that I'm using to generate the items in a menu bar. There I wanted to make certain as we added new menu items in QA or production that I could easily merge the two when needed without worrying about ID conflicts. This shared-sequence approach worked in that situation like a charm.

2 comments
KennethNYC
KennethNYC

Dear Rex i am too is new to Oracle's SQL language. I am curious when you declare "if :new.id is null then" what does the ":" means ?

RexWorld
RexWorld

I'd love to hear any other uses you might have found for this ability to share sequences across database instances, or across multiple tables in the same instance.

Editor's Picks