DBA Hub

📋Steps in this guide1/3

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

Replace trigger-based population of numeric ID columns with the new identity functionality in Oracle Database 12c Release 1.

oracle 12cconfigurationintermediate
by OracleDba
15 views
1

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. 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 privilege. Without it, attempts to define an identity column will produce a "ORA-01031: insufficient privileges" error. Using forces the use of the identity. If an insert statement references the identity column, even to specify a NULL value, an error is produced. Using 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 . Using allows the identity to be used if the identity column is referenced, but a value of NULL is specified. Based on the requirement for the privilege, it is not difficult to deduce that a sequence is being used to populate the identity column. The views show information about identity columns. The link between the table and the sequence is stored in the table. Sequence usage is now visible in execution plans.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

GRANT CREATE TABLE, CREATE SEQUENCE TO test;
CONN test/test@pdb1

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>

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>

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>

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>

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>

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>

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      |	     |	     |		  |	     |
----------------------------------------------------------------------------------------------
2

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 and 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 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 to populate it.
3

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. 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. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
-- 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)
);

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!