Identity Columns in Oracle Database 12c Release 1 (12.1)

In previous releases of the Oracle database, there was no direct equivalent of the AutoNumber or Identity functionality of other database engines. Instead, this behaviour had to be implemented using a combination of sequences and triggers. Oracle 12c introduces two alternatives to this by providing identity columns and the ability to use sequence pseudocolumns as default values. This article will focus on the use of identity columns.

Identity Columns
Restrictions
Performance

Identity Columns

The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is shown below.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

Ignoring the identity_options, which match those of the CREATE SEQUENCE statement, this syntax allows us to use three variations on the identity functionality.

Before we can look at some examples, you need to make sure your test user has the CREATE SEQUENCE privilege. Without it, attempts to define an identity column will produce a "ORA-01031: insufficient privileges" error.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
GRANT CREATE TABLE, CREATE SEQUENCE TO test;
CONN test/test@pdb1

Using ALWAYS forces the use of the identity. If an insert statement references the identity column, even to specify a NULL value, an error is produced.

DROP TABLE identity_test_tab PURGE;
CREATE TABLE identity_test_tab (
 id NUMBER GENERATED ALWAYS AS IDENTITY,
 description VARCHAR2(30)
);
SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
 *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')
 *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
SQL>

Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement, but if the column is referenced, the specified value will be used in place of the identity. Attempting to specify the value NULL in this case results in an error, since identity columns are always NOT NULL.

DROP TABLE identity_test_tab PURGE;
CREATE TABLE identity_test_tab (
 id NUMBER GENERATED BY DEFAULT AS IDENTITY,
 description VARCHAR2(30)
);
SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
 *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."IDENTITY_TEST_TAB"."ID")
SQL>

Using BY DEFAULT ON NULL allows the identity to be used if the identity column is referenced, but a value of NULL is specified.

DROP TABLE identity_test_tab PURGE;
CREATE TABLE identity_test_tab (
 id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
 description VARCHAR2(30)
);
SQL> INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
1 row created.
SQL> INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
1 row created.
SQL> SELECT * FROM identity_test_tab;
	ID DESCRIPTION
---------- ------------------------------
	 1 Just DESCRIPTION
 999 ID=999 and DESCRIPTION
	 2 ID=NULL and DESCRIPTION
SQL>

Based on the requirement for the CREATE SEQUENCE privilege, it is not difficult to deduce that a sequence is being used to populate the identity column.

COLUMN object_name FORMAT A20
SELECT object_name, object_type
FROM user_objects;
OBJECT_NAME OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_92117 SEQUENCE
IDENTITY_TEST_TAB TABLE
2 rows selected.
SQL>

The [DBA|ALL|USER]_TAB_IDENTITY_COLS views show information about identity columns.

SET LINESIZE 100
COLUMN table_name FORMAT A20
COLUMN column_name FORMAT A15
COLUMN generation_type FORMAT A10
COLUMN identity_options FORMAT A50
SELECT table_name, 
 column_name,
 generation_type,
 identity_options
FROM all_tab_identity_cols
WHERE owner = 'TEST'
ORDER BY 1, 2;
TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- --------------------------------------------------
IDENTITY_TEST_TAB ID ALWAYS START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
 999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
 , CACHE_SIZE: 20, ORDER_FLAG: N
SQL>

The link between the table and the sequence is stored in the SYS.IDNSEQ$ table.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;
COLUMN sequence_name FORMAT A30
SELECT a.name AS table_name,
 b.name AS sequence_name
FROM sys.idnseq$ c
 JOIN obj$ a ON c.obj# = a.obj#
 JOIN obj$ b ON c.seqobj# = b.obj#;
TABLE_NAME	 SEQUENCE_NAME
-------------------- ------------------------------
IDENTITY_TEST_TAB ISEQ$$_92117
SQL>

Sequence usage is now visible in execution plans.

SET AUTOTRACE ON
SET LINESIZE 200
INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');
Execution Plan
----------------------------------------------------------
Plan hash value: 993166116
----------------------------------------------------------------------------------------------
| Id | Operation		 | Name 	 | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT	 |		 |	 1 |	 100 |	 1 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | IDENTITY_TEST_TAB |	 |	 |		 |	 |
| 2 | SEQUENCE		 | ISEQ$$_92117 |	 |	 |		 |	 |
----------------------------------------------------------------------------------------------

Restrictions

There are a number of restrictions associated with identity columns, listed on the documentation here.

One identity column per table.
Identity columns must be numeric types, and can't be user-defined data types.
Identity columns can't have a default clause.
Identity columns are implicitly have NOT NULL and NOT DEFERRABLE constraints. They can't be explicitly alter to anything else.
From the doc, "If an identity column is encrypted, then the encryption algorithm may be inferred. Oracle recommends that you use a strong encryption algorithm on identity columns."
The CREATE TABLE ... AS SELECT will not inherit the identity property on a column. This is true for several structural definitions. If you care about structure, you should always CREATE TABLE, then use INSERT INTO ... SELECT to populate it.

Performance

The following tables will allow us to compare the performance of the identity column against direct use of a sequence and a trigger-based solution.

-- Create a table with an old-style identity column populated using a trigger.
CREATE TABLE trigger_identity (
 id NUMBER NOT NULL,
 description VARCHAR2(30)
);
CREATE SEQUENCE trigger_identity_seq;
CREATE OR REPLACE TRIGGER trigger_identity_bir 
BEFORE INSERT ON trigger_identity 
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
 :new.id := trigger_identity_seq.NEXTVAL;
END;
/
-- Populate the column directly using a sequence.
CREATE TABLE sequence_identity (
 id NUMBER NOT NULL,
 description VARCHAR2(30)
);
CREATE SEQUENCE sequence_identity_seq;
-- Create a table with a real identity column.
CREATE TABLE real_identity (
 id NUMBER GENERATED ALWAYS AS IDENTITY,
 description VARCHAR2(30)
);

The following script compares the insert performance of the three tables. The first test uses the trigger to populate the ID column. The second test references a sequence directly, rather than relying on a trigger. The third uses the new identity column functionality.

SET SERVEROUTPUT ON
DECLARE
 l_time PLS_INTEGER;
 l_cpu PLS_INTEGER;
 TYPE t_data IS TABLE OF trigger_identity.description%TYPE;
 l_data t_data;
BEGIN
 -- Popluate a collection with some dummy data.
 SELECT 'DUMMY DATA'
 BULK COLLECT INTO l_data
 FROM dual
 CONNECT BY level <= 10000;
 -- Trigger-based solution.
 EXECUTE IMMEDIATE 'TRUNCATE TABLE trigger_identity';
 l_time := DBMS_UTILITY.get_time;
 l_cpu := DBMS_UTILITY.get_cpu_time;
 FORALL i IN l_data.first .. l_data.last
 INSERT INTO trigger_identity (description) VALUES (l_data(i));
 
 DBMS_OUTPUT.put_line('TRIGGER_IDENTITY : ' ||
 'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
 'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 -- Direct use of a sequence.
 EXECUTE IMMEDIATE 'TRUNCATE TABLE sequence_identity';
 l_time := DBMS_UTILITY.get_time;
 l_cpu := DBMS_UTILITY.get_cpu_time;
 FORALL i IN l_data.first .. l_data.last
 INSERT INTO sequence_identity (id, description) VALUES (sequence_identity_seq.NEXTVAL, l_data(i));
 
 DBMS_OUTPUT.put_line('SEQUENCE_IDENTITY: ' ||
 'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
 'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 -- Using an identity column.
 EXECUTE IMMEDIATE 'TRUNCATE TABLE real_identity';
 l_time := DBMS_UTILITY.get_time;
 l_cpu := DBMS_UTILITY.get_cpu_time;
 FORALL i IN l_data.first .. l_data.last
 INSERT INTO real_identity (description) VALUES (l_data(i));
 
 DBMS_OUTPUT.put_line('REAL_IDENTITY : ' ||
 'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
 'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 
END;
/
TRIGGER_IDENTITY : Time=217 hsecs CPU Time=204 hsecs
SEQUENCE_IDENTITY: Time=26 hsecs CPU Time=22 hsecs
REAL_IDENTITY : Time=28 hsecs CPU Time=26 hsecs
PL/SQL procedure successfully completed.
SQL>

Not surprisingly, trigger-based test performs much worse than the others. The direct use of a sequence and the 12c identity column give comparable results, which are typically an order of magnitude faster than using a trigger to populate the ID column.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)