Implicit Sequences

Implicit sequences were introduced in Oracle 12.1.0.1

In Oracle 12c you can specify sequences with the NEXTVAL or CURRVAL pseudo-columns as default column values. When a new row is inserted into the table, the sequence is automatically incremented and assigned to the column. In this page, I have called this new feature "implicit sequences". However, this name is technically incorrect as the sequences are not themselves implicit, only the assignment of default values to the affected column(s).

Example

Here is an example. First we must create a sequence. For example:

CREATE SEQUENCE seq1;

In Oracle 12c, as it earlier versions, by default, sequences are created with a cache size of 20. We can then create a table that references the sequence. For example:

CREATE TABLE transaction
(
  transaction_id   NUMBER DEFAULT seq1.NEXTVAL,
  holding_id       NUMBER,
  transaction_type NUMBER,
  transaction_date DATE,
  credit_value     NUMBER,
  debit_value     NUMBER
);

Note that the DEFAULT clause includes the sequence name and the NEXTVAL pseudo-column. When a row is inserted into the table, the TRANSACTION_ID column will automatically be set to the next value in the sequence. Assume that a few rows have already been added to the table. For example:

SELECT MAX(transaction_id) FROM transaction;

MAX(TRANSACTION_ID)
-------------------
                 41

INSERT INTO transaction
(holding_id,transaction_type,transaction_date,credit_value,debit_value)
VALUES
(10,1,SYSDATE,100,0);

SELECT MAX(transaction_id) FROM transaction;


MAX(TRANSACTION_ID)
-------------------
                 42

Note that the INSERT statement does not include the TRANSACTION_ID column. The default value for this column is generated internally.

Sequence Gaps

You cannot use the above syntax to guarantee that sequence numbers will be consecutive. The sequence is cached in the SGA, but is flushed out fairly regularly, so the next INSERT may yield the following:

SELECT MAX(transaction_id) FROM transaction;

MAX(TRANSACTION_ID)
-------------------
                 42

INSERT INTO transaction
(holding_id,transaction_type,transaction_date,credit_value,debit_value)
VALUES
(11,2,SYSDATE,0,200);

SELECT MAX(transaction_id) FROM transaction;

MAX(TRANSACTION_ID)
-------------------
                 46

Specifying the sequence owner

In the DEFAULT clause, the sequence name can optionally be prefixed by the sequence owner. For example:

CREATE TABLE transaction
(
  transaction_id   NUMBER DEFAULT us01.seq1.NEXTVAL,
  holding_id       NUMBER,
  transaction_type NUMBER,
  transaction_date DATE,
  credit_value     NUMBER,
  debit_value     NUMBER
);

Specifying an owner for the sequence will increase resilience at the possible expense of flexibility.

Using synonyms for the sequence

You can also specify a public or private synonym for the sequence. For example:

CREATE PUBLIC SYNONYM syn1 FOR us01.seq1;

CREATE TABLE transaction
(
  transaction_id   NUMBER DEFAULT syn1.NEXTVAL,
  holding_id       NUMBER,
  transaction_type NUMBER,
  transaction_date DATE,
  credit_value     NUMBER,
  debit_value     NUMBER
);

When the table is created, the synonym will be resolved. The result is stored in the DEFAULT$ column of the COL$ table in the data dictionary. For example:

SELECT default$
FROM sys.col$
WHERE obj# =
(
  SELECT obj# FROM sys.obj$
  WHERE owner# =
  (
    SELECT user# FROM sys.user$
    WHERE name = 'US01'
  )
  AND name = 'TRANSACTION'
)
AND name = 'TRANSACTION_ID';

DEFAULT$
--------------------------------------------------
"US01"."SEQ1"."NEXTVAL"

Altering an existing table

If the table already exists, the default value for the column can be modified. For example:

ALTER TABLE transaction
MODIFY transaction_id DEFAULT seq1.NEXTVAL;

Rollback

If a transaction is rolled back, all sequence numbers allocated by that transaction will be lost.

Error Conditions

The sequence specified by the INSERT statement must exist and the user executing the INSERT statement must have permissions to increment the sequence. If the sequence does not exist or is not accessible when the table is created, an error will be raised. For example:

DROP SEQUENCE seq1;

CREATE TABLE transaction
(
  transaction_id   NUMBER DEFAULT seq1.NEXTVAL,
  holding_id       NUMBER,
  transaction_type NUMBER,
  transaction_date DATE,
  credit_value     NUMBER,
  debit_value     NUMBER
);

  transaction_id   NUMBER DEFAULT seq1.NEXTVAL,
                                  *
ERROR at line 3:
ORA-02289: sequence does not exist

If the sequence is accessible when the table is created, but then is subsequently dropped or becomes inaccessible, then an error will be raised the next time a row is inserted. For example:

DROP SEQUENCE seq1;

INSERT INTO transaction
(holding_id,transaction_type,transaction_date,credit_value,debit_value)
VALUES
(25,2,SYSDATE,0,300);

INSERT INTO transaction
       *
ERROR at line 1:
ORA-02289: sequence does not exist

Internal Implementation

Under the covers, the internal implementation is similar to explicit sequence assignment. If the sequence is not currently in the library cache, then it is obtained using the following recursive statement:

select increment$,minvalue,maxvalue,cycle#,order$,cache,
highwater,audit$,flags,partcount from seq$ where obj#=:1

When the cache is exhausted the server process is responsible for issuing a recursive transaction to obtain the next batch of sequence numbers.

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,
highwater=:8,audit$=:9,flags=:10,partcount=:11 where obj#=:1

Summary

This is a neat little feature that has been introduced about 20 years too late. In my opinion, it could easily have been implemented in Oracle 6.0. It could be useful for new applications, though the amount of time and effort saved is negligible. For existing applications the relevance of this new feature is more questionable; sites rarely wish to modify code that already works in case they introduce new problem. Also it is not always advisable to mix coding styles within the same application. Therefore I think this use of this feature will be relatively rare for the next few years.