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
用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。
START WITH
定义Oracle序列的初始值(即产生的第一个值)，默认为1。
MAXVALUE
定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。
MINVALUE
定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，
CYCLE和NOCYCLE
表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
CACHE
(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
原文地址：
ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能