Oracle列自增实现(2)-Identity Columns in Oracle Database 12c Release 1 (12.1)

Oracle列自增-Identity Columns in Oracle Database 12c Release 1 (12.1)

在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能。

一、Identity Columns使用语法

GENERATED

[ ALWAYS | BY DEFAULT [ ON NULL ] ]

AS IDENTITY [ ( identity_options ) ]identity_options

二、identity_clause

2.1 ALWAYS选项

DROP TABLE IDENTITY_TEST_TAB PURGE;

CREATE TABLE identity_test_tab (

id NUMBER GENERATED ALWAYS AS IDENTITY,

description VARCHAR2(30)

);

插入测试1：

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION')

[SQL]INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION')

受影响的行: 1

时间: 0.008s

插入测试2：

INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

[SQL]INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

[Err] ORA-32795: cannot insert into a generated always identity column 无法插入到“始终生成”身份列

插入测试3：

INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

[SQL]INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

[Err] ORA-32795: cannot insert into a generated always identity column

更新测试：

UPDATE IDENTITY_TEST_TAB SET ID=2 WHERE ID=1

[SQL]UPDATE IDENTITY_TEST_TAB SET ID=2 WHERE ID=1

[Err] ORA-32796: cannot update a generated always identity column

结论：

1. GENERATED ALWAYS AS IDENTITY 可以不指定该列进行插入
2. GENERATED ALWAYS AS IDENTITY不能在该列中插入NULL值
3. GENERATED ALWAYS AS IDENTITY不能指定具体值插入
4. GENERATED ALWAYS AS IDENTITY 不能使用update更新该列

2.2 BY DEFAULT选项

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (

id NUMBER GENERATED BY DEFAULT AS IDENTITY,

description VARCHAR2(30)

);

插入测试1：

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

[SQL]INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

受影响的行: 1

时间: 0.001s

SELECT * FROM identity_test_tab;

插入测试2：

INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

[SQL]INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

受影响的行: 1

时间: 0.001s

SELECT * FROM identity_test_tab;

插入测试3：

INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

[SQL]INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

[Err] ORA-01400: cannot insert NULL into ("TEST_USER"."IDENTITY_TEST_TAB"."ID")

更新测试：

UPDATE IDENTITY_TEST_TAB SET ID=2 WHERE ID=1

[SQL]UPDATE IDENTITY_TEST_TAB SET ID=2 WHERE ID=1

受影响的行: 1

时间: 0.001sUPDATE IDENTITY_TEST_TAB SET ID=2 WHERE ID=1

     结论:

1. GENERATED BY DEFAULT AS IDENTITY 可以不指定该列进行插入
2. GENERATED BY DEFAULT AS IDENTITY 可以指定具体值插入
3. GENERATED BY DEFAULT AS IDENTITY 不能在该列中插入null值
4. 可以使用update更新该列，但不能更新为NULL

2.3 DEFAULT ON NULL选项

DROP TABLE identity_test_tab PURGE;

CREATE TABLE identity_test_tab (

id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,

description VARCHAR2(30)

);

插入测试：

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

[SQL]INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION')

受影响的行: 1

时间: 0.003s

[SQL]INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION')

受影响的行: 1

时间: 0.001s

[SQL]INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')

受影响的行: 1

时间: 0.002s

SELECT * FROM identity_test_tab;

更新测试

UPDATE IDENTITY_TEST_TAB SET ID=3 WHERE ID=1

[SQL]UPDATE IDENTITY_TEST_TAB SET ID=3 WHERE ID=1

受影响的行: 1

时间: 0.004s

     结论:

1. GENERATED BY DEFAULT ON NULL AS IDENTITY 可以不指定该列进行插入
2. GENERATED BY DEFAULT ON NULL AS IDENTITY 方式可以指定具体值插入
3. GENERATED BY DEFAULT ON NULL AS IDENTITY 可以在该列中插入null值
4. 可以使用update更新该列

三、原理

3.1 Identity Columns 是基于序列实现的

使用此语法实现ID自增，要求必须有创建序列的权限。可以推测是基于序列实现的

执行完建表语句后：

CREATE TABLE identity_test_tab (

id NUMBER GENERATED ALWAYS AS IDENTITY,

description VARCHAR2(30)

);

查看user_objects

SELECT object_name, object_type FROM user_objects;

发现建表的时候自动生成了一个sequence

SELECT table_name, 

column_name,

generation_type,

identity_options

FROM all_tab_identity_cols

WHERE owner = 'TEST_USER';

表和sequence的关系存在SYS.IDNSEQ$表中

Sys登陆查看

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#

where a.name='IDENTITY_TEST_TAB';

3.2 GENERATED IDENTITY 中sequence不能单独被删除

DROP TABLE IDENTITY_TEST_TAB

删除表后，该sequence还存在。且该sequence无法被删除

必须

purge table IDENTITY_TEST_TAB，

结论：

1. Identity Columns 是基于序列实现的
2. GENERATED IDENTITY 中sequence不能单独被删除
3. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除

3.3 执行插入语句时的解释计划

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

对比发现：该方式的效率比触发器+序列的方式高！

四、identity_options

查看表的DDL

select dbms_metadata.get_ddl('TABLE','IDENTITY_TEST_TAB') FROM DUAL;

CREATE TABLE "TEST_USER"."IDENTITY_TEST_TAB"

( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOT NULL ENABLE,

"DESCRIPTION" VARCHAR2(30)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

INCREMENT BY

用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。

START WITH

定义Oracle序列的初始值(即产生的第一个值)，默认为1。

MAXVALUE

定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

MINVALUE

定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，

CYCLE和NOCYCLE

表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

CACHE

(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

原文地址：

ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能

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

Database SQL Language Reference