The series so far:

1. Oracle sequences: The basics
2. Oracle sequences: RAC
3. Typical uses of Oracle sequences
4. Oracle sequences – 12c features including “identity” 

The final installment of this mini-series on sequences looks at the new features relating to sequences that appeared in the 12c timeline in response, possibly, to some of the more frequently voiced complaints of users and developers. In particular, the article shows how it is now possible to use synthetic keys for a table through a sequence that is implicitly created and tied to that table by a static declaration.

Feature round-up

Since Oracle 12.2, and all the way up to 21c, the complete syntax of the create sequence command (excluding only the details relating to creating sequences that can be shared from an application root to its application PDBs) has been as follows:

There is a new option for altering a sequence that appeared in 12.1 but was not documented until 18c:

The significant new feature that goes along with sequences, though, is that you can (at last!) define one column in a table to be an identity column. Having an identity column in a table means a sequence will be created and linked to the column behind the scenes so that inserts into the table will automatically use the sequence nextval to populate that column when needed.

Basic options

Of the four newer options highlighted above for create sequence, I’ve covered the scale/extend option in an earlier installment discussing RAC. If you specify scale, the sequence generator uses six leading digits to include the instance and session information. Unless you include the extend option, these six digits will be considered as contributing to your stated maxvalue – which means your effective range of values has been reduced by a factor of a million.

The shard option does for sharded databases what the scale option does for RAC instances. It uses the four leading digits of the generated value for the shard id – presumably to allow you to avoid cross-shard updates taking place when sequence values are used in indexes. As with scale, shard can include the extend option, but if you use both scale and shard, then extend needs only appear once in the identity declaration.

It’s worth noting that the manuals advise you against using the order option if you’ve used the scale or shard options. Since their purpose is to minimize cross instance and cross shard calls, this makes perfect sense as the order option would result in the instances/shards constantly communicating with each other to pass the current high value around.

The keep option is a detail you need to know about if you’re implementing Oracle’s Application Continuity features. Since nextval (like sysdate, systimestamp, and a couple of others) is not a transactional value, Oracle needs to do something special to ensure that transactions can be replayed correctly in the event of an outage. To allow this to happen, the sequence needs to be declared with the keep option, and application users need the keep sequence privilege, granted through a command like:

Finally, there is the global/session option. The default is global, which behaves in the way that create sequence has always behaved. The session option means the sequence will be a “temporary” sequence (rather like a global temporary table). When a session selects nextval from a session sequence, it will create a private copy of the sequence from the sequence metadata and work only with that private copy. If multiple sessions are using the same session sequence, they will all have their own private copies and all start at the same starting value without interfering with each other.

There are several benefits to session sequences when it’s appropriate to use them). You no longer have to worry about updates to the seq$ table (which is particularly convenient for RAC systems), and you can now use sequences on read-only databases (provided the database was read-write at some point so that you could create the sequence).

This feature may be particularly relevant to systems using Active Data Guard. With a suitable sequence definition at the primary database, a standby database can request a range (dictated by the cache size) of values from the primary that will not duplicate values that will be used by the primary. If you can define the sequence as a session sequence at the primary database, the standby no longer has to access the primary but starts by accessing its local definition. Taking this further: with a global temporary table (originally defined at the primary, of course), and with temp_undo_enabled set to true (which is the default for ADG standbys), you can even execute DML like the following on GTTs:

insert into my_gtt1 select session_seq1.nextval, {other columns} from …

I have to admit that I haven’t set up a system to look for side effects and unexpected overheads in this approach – so do test it and examine the session events and session activity stats very carefully before taking it to production.

Another benefit of the session-private sequence is that since you are no longer competing for a public sequence cache, the latch contention due to heavy usage of a very popular sequence will be reduced. I was a little disappointed, however, when I tested the feature on 19.11.0.0: a global sequence needed 3 latch gets per call to nextval while a session sequence still needed 2 latch gets – an improvement, but not as much of an improvement as I had assumed that I would see. Possibly the latch gets are currently needed to check that the sequence definition hasn’t changed since the last call.

A detail to remember when you define a session sequence is that some standard sequence options (cache/nocache, order/noorder) are irrelevant and will be ignored.

Restart

One of the FAQs about sequences used to be “how can I reset the sequence?” The answer was usually something along the lines of

1. work out the difference between the current value and the start with value,
2. alter the increment by to the negative of that difference
3. select nextval from dual;
4. alter the increment by back to its original value

This method was a little risky since someone else might manage to slip in a select nextval in the brief gap between steps 2 and 4, leaving you with a sequence that was nowhere near where you wanted it to be. In 12.1.0.2, one of the options in the alter sequence command is to restart.

Unfortunately, if you try this on a base-level 12.1.0.2, you will find that Oracle raises an error:

The dictionary table for sequences (seq$) has a column highwater declared as NOT NULL, and it’s easy to see from a trace file that the internal code to restart a sequence doesn’t supply a value for this column in its call to update the seq$ table. This is probably bug 19602919, for which a patch is available on several platforms, but the bug is unpublished, so I haven’t been able to confirm the details.

There are a couple of little traps to the restart option. First, if you restart a session sequence, you will find that every session that is currently using that sequence will immediately see the effects of the restart; the restart is not restricted to just your own session. I’d like to see the restart made completely private, but since it’s just one of many options in the alter sequence command, I can understand why it might be difficult to make a special case for it. Of course, for similar reasons, you can’t restart a sequence in a read-only database – Oracle will raise the error you might expect: ORA-16000: database or pluggable database open for read-only access.

The second trap appears with incomplete statements of action. Here’s an example (run on 19.11.0.0):

What values do you think the four calls for s1.nextval will produce? You may be expecting 100, 102, 100, 102. However, it’s actually 100, 102, 10, 12. Oracle doesn’t remember the original start with clause; it restarts at your minvalue unless you explicitly include a start with value in the call to restart. Perhaps you guessed correctly what would happen because I supplied a hint by explicitly setting the minvalue to a non-default value If I hadn’t done that, would you have guessed that Oracle would restart at the default minvalue of 1?

Identity

One of the questions that used to appear regularly on Oracle forums was: “How do I find out which sequence is being used to generate the key for this table?” The most typical answer was that there was no “tight” connection between sequences and primary keys – you create a sequence, you write code (often seen in a trigger) to select from the sequence and populate the column.

After many years Oracle allowed schema_name.sequence_name.nextval to be used as a default value for a column, making the connection much tighter. This didn’t stop anyone from using the nominated sequence for other reasons, even to the extent of making it the default value for columns in several other tables.

Finally, in 12cR1, Oracle introduced the “identity” mechanism, which sets up a rigid link between a column and a system-generated sequence. There can be only one identity column in a table, and the sequence cannot be accessed in any way other than Oracle’s internal access to populate the column. The one identity per table rule is enforced by the simple implementation detail that the name of the sequence includes the object_id of the table in the format: ISEQ$$_{table_object_id}

The identity column doesn’t conform 100% to expectations, however. It doesn’t automatically get declared unique, though it automatically acquires a not null declaration and constraint. If you want the column to be used as the primary key, you have to declare it as such.

There are three variants on adding an identity column to a table – shown below in a single statement with two of the variants commented out:

The example creates a table with a single numeric (integer) column called n1. It adds a column called ident, which I’ve defined as a numeric (integer) column and declared as an identity column. Although I’m not allowed to name the sequence that will supply values for the column, I’ve shown that I’m allowed to supply standard sequence options that dictate how the values will be generated.

To explain how the three identity variants (always, by default, by default on null) work, I can insert three carefully constructed rows into the three versions of the table and see what the results look like when I select them back. Here are the three insert statements:

In the first case, I haven’t inserted the ident column at all.

In the second case, I’ve inserted an explicit null into the ident column

In the last case, I’ve inserted a non-null value into the ident column

Here are the three sets of effects from the three separate tests (with a little cosmetic editing for ease of reading):

Generated always

In this first case, two of the inserts fail because I’ve supplied an input (albeit that one of them is an explicit null) for the ident column. The first of the three inserts didn’t try to supply anything at all for ident and has used the sequence to generate the value 10.

Generated by default

In the second case, Oracle used the sequence to generate the value 10 when I didn’t include ident in my insert statement, and it accepted the actual value I’d supplied for ident for the third statement. However, when I tried to insert a row where the ident column was supplied with an explicit null, Oracle rejected it. The column acquired a not null declaration as it became an identity, so I can’t insert a null, but Oracle is only allowed to use the default value if the insert hasn’t attempted to supply anything at all for the column.

Generated by default on null

Finally, I have a version where all three inserts are acceptable. The insert which doesn’t supply an input for ident is supplied with a value by the sequence. The insert with the explicit null supplied for ident is overwritten by a value supplied by the sequence, and the insert with a non-null ident accepts that value.

There are other restrictions on the ident column that vary with the identity declaration. You can’t update the identity column if it is declared generated always, but you can update it if it’s generated by default or generated by default on null, provided you don’t try to update it to null.

It’s nice to have a choice of strategies, and the best choice for your application may depend on the style of code that has already been written. You may also decide that you want to change an existing application that uses an older mechanism (such as a simple default sequence.nextval declaration) to take advantage of the new identity mechanism This might mean you have to convert your existing data using one mechanism, then go into production using another – e.g., conversion using by default on null then changing to always when the data is complete.

Add/Drop/Modify identity

Despite the heading, I’m going to start with dropping an identity because it’s the easy option:

That’s it – the sequence is dropped. The not null declaration and constraint disappear, but the column is still there. Big warning, though: if you do this, you can’t just put it back again. You can’t modify an existing column to become an identity; you can only add a new identity column.

This restriction seems like a bit of a show-stopper to me (and I may be wrong), but it looks as if you can’t take a table with a user-coded “identity-like” column and tell Oracle “I want this column to become an Oracle-managed identity.”

You’ve already seen the example of adding an identity to a table. However, in my original example, I created a table and added the identity column while it was still empty, and that hides a threat. If you want to add an identity column to a table with existing data, then Oracle will lock the table before using the generated sequence to update every row. This may lead to some rows being migrated, of course, especially if you’ve used the scale extend option.

It is possible to modify the definition of an identity – mainly to allow you to correct errors in the way you’ve defined the underlying sequence: a larger cache size is the most likely requirement. Imagine, however, that you’ve used by default or by default on null in your identity definition. This would have allowed your application to insert values greater than the current value of the underlying sequence.

Now consider the possibility that you’ve created a primary key index on that column. What are you going to do when the inevitable collision arrives, and the supporting sequence reaches a value that has previously been manually entered? You’re going to get a “duplicate key” error and will need to adjust the sequence to bypass the value. Though it’s not documented in the earlier manuals, there’s a special case for start with when a sequence is associated with an identity column:

The special case of limit value tells Oracle to scan the table for the largest value in the column and jump the sequence to the next value above that.

Oracle 12c sequences

This final article on sequences has provided a brief tour of the scale, shared, keep, and session options for sequences. The scale option was covered in more detail in an earlier article, and the shard and keep options are likely to be used by only a small set of more sophisticated users. The session option for creating “private” sequences that could be used in read-only databases is the one most likely to be of general use.

I’ve highlighted the appearance (after a very long wait) of the restart option for sequences and have highlighted a couple of little traps with its use: where will a sequence restart from, and what side effects will a restart have for a session sequence that is currently in use in multiple sessions.

Finally, the article covered the identity column declaration and how it is dependent on a system-generated sequence then used a simple example to show the effects of the three different definitions on data input. Two of the definitions allow for data creation that could cause a primary key (or unique) constraint to raise an exception, so the article explained the special option for modifying the sequence to jump above the boundary value in the column. Also noted, that it’s easy to remove the identity mechanism from a column, but a lot more work to add the identity mechanism to an existing data set.