Identity Columns in Oracle Database 12c

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

Related articles.

• AutoNumber, Identity and Using Sequences as Default Values in Oracle
• DEFAULT Values for Table Columns : Enhancements in Oracle Database 12c Release 1 (12.1)
• AutoNumber And Identity Functionality in Oracle Databases (Pre-12c)

Identity Columns

The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show 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 minor restrictions associated with identity columns. Rather than repeat the documentation, you can read them here.

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>

출처

https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1