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

浏览数：46 / 时间：2015年06月12日

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

结论：

GENERATED ALWAYS AS IDENTITY 可以不指定该列进行插入
GENERATED ALWAYS AS IDENTITY不能在该列中插入NULL值
GENERATED ALWAYS AS IDENTITY不能指定具体值插入
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

结论:

GENERATED BY DEFAULT AS IDENTITY 可以不指定该列进行插入
GENERATED BY DEFAULT AS IDENTITY 可以指定具体值插入
GENERATED BY DEFAULT AS IDENTITY 不能在该列中插入null值
可以使用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

结论:

GENERATED BY DEFAULT ON NULL AS IDENTITY 可以不指定该列进行插入
GENERATED BY DEFAULT ON NULL AS IDENTITY 方式可以指定具体值插入
GENERATED BY DEFAULT ON NULL AS IDENTITY 可以在该列中插入null值
可以使用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，

结论：

Identity Columns 是基于序列实现的
GENERATED IDENTITY 中sequence不能单独被删除
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