Identity Columns

标识列是12c的一个新特性，能自动生成列里的值，一部分功能与mysql中的自增列有点类似，简化了应用程序的开发和其他关系型数据库迁移到oracle。这个特性主要是利用一个默认的序列生成器，通过这个生成器来绝对字段增加或者减少的整数值。

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table t1 purge;
create table t1
(id number generated by default as identity,
name varchar2(10));

insert into t1 (name) values ('xb');
commit;

select * from t1;

        ID NAME
---------- ----------
         1 xb

对于标识列来说，我们要么指定一个默认的序列去生成字段值或者只对那些插入NULL的情况生成字段值。根据标识列的定义可以有GENERATED ALWAYS和GENERATED BY DEFAULT关键字。

GENERATED ALWAYS

oracle使用序列生成器来确定列值，但也可以显示的指定某个值。如果指定了ON NULL关键字那么序列生成器会在你插入NULL值的时候生成一个值给字段。

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
drop table t1 purge;
create table t1
(id number generated by default as identity,
name varchar2(10));

insert into t1 (name) values ('xb');
commit;

xb@PDB12C> insert into t1 values(null,'xb2');
insert into t1 values(null,'xb2')
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XB"."T1"."ID")

select * from t1;

        ID NAME
---------- ----------
         1 xb

xb@PDB12C> @desc t1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                              NOT NULL NUMBER
    2      NAME                                     VARCHAR2(10)

可以看到通过序列生成器生成的字段在创建的时候默认就是NOT NULL属性，因为这里没有对NULL的情况进行指定，所以序列生成器在碰到NULL的情况无法生成值，则会报错

加上ON NULL关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table t1 purge;
create table t1
(id number generated by default on NULL as identity,
name varchar2(10));

insert into t1 values(1,'xb');
insert into t1 values(9,'xb2');
insert into t1 values(null,'xb3');
commit;

xb@PDB12C> select * from t1;

        ID NAME
---------- ----------
         1 xb
         9 xb2
         1 xb3

这样当插入的字段值为NULL时，生成器则会自动生成一个整数值给字段。通过执行计划也可以看到，在对有序列生成器属性的表插入数据时，其实是访问的序列

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
xb@PDB12C> insert into t1 values(null,'xb3');

1 row created.

xb@PDB12C> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4b1kmg40ra83t, child number 0
-------------------------------------
insert into t1 values(null,'xb3')

Plan hash value: 948190082

---------------------------------------------------------
| Id  | Operation                | Name         | Cost  |
---------------------------------------------------------
|   0 | INSERT STATEMENT         |              |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | T1           |       |
|   2 |   SEQUENCE               | ISEQ$$_90071 |       |
---------------------------------------------------------

   - cpu costing is off (consider enabling it)


16 rows selected.

# 而这个序列也是真实存在的，说明是用户自动创建的
xb@PDB12C> select * from user_sequences;

SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S K
-------------------- ---------- ---------- ------------ - - ---------- ----------- - - - -
ISEQ$$_90071                  1 1.0000E+28            1 N N         20          21 N N N N

Generated Always

字段无法被用户所更新，所以序列生成器生成的值是列唯一可能的值，如果像之前一样指定某个值或修改都会报错

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
drop table t2 purge;
create table t2
(id number generated always as identity,
name varchar2(10));

xb@PDB12C> insert into t2 values(1,'xb');
insert into t2 values(1,'xb')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

xb@PDB12C> insert into t2 values(null,'xb');
insert into t2 values(null,'xb')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

insert into t2(name) values('xb');
insert into t2(name) values('xb2');
insert into t2(name) values('xb3');
commit;

xb@PDB12C> select * from t2;

        ID NAME
---------- ----------
         1 xb
         2 xb2
         3 xb3

xb@PDB12C> update t2 set id=4 where name='xb';
update t2 set id=4 where name='xb'
              *
ERROR at line 1:
ORA-32796: cannot update a generated always identity column

由于是使用的序列，那么也可以用到一些序列中的特性，可以指定不同的初始值和累加值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table t3 purge;
create table t3
(id number generated always as identity (start with 2 increment by 2),
name varchar2(10));

insert into t3(name) values('xb');
insert into t3(name) values('xb2');
insert into t3(name) values('xb3');
commit;

xb@PDB12C> select * from t3;

        ID NAME
---------- ----------
         2 xb
         4 xb2
         6 xb3

通过USER_TAB_IDENTITY_COLS等视图可以查询到一些标识列的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
set linesize 300
col table_name for a10
col COLUMN_NAME for a10
col IDENTITY_OPTIONS for a70
select * from USER_TAB_IDENTITY_COLS;

TABLE_NAME COLUMN_NAM GENERATION SEQUENCE_NAME        IDENTITY_OPTIONS
---------- ---------- ---------- -------------------- ----------------------------------------------------------------------
T3         ID         ALWAYS     ISEQ$$_90075         START WITH: 2, INCREMENT BY: 2, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

T1         ID         BY DEFAULT ISEQ$$_90079         START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

T2         ID         ALWAYS     ISEQ$$_90073         START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

这个特性目前也有一些限制条件：

  • 每张表只能由一个标识列
  • 标识列的数据类型必须是数字型，而且不能是用户自定义的类型
  • 标识列中不能有DEFAULT关键字
  • 标识列中，NOT NULL和NON DEFERRABLE约束都是隐式存在
  • CTAS语句无法继承字段的标识属性
12c新特性
#12c
Identity Columns
https://www.xbdba.com/2019/07/22/identity-columns/
作者
xbdba
发布于
2019年7月22日
许可协议