Oracle 数据库 IDENTITY 列

IDENTITY列是Oracle数据库12c推出的新特性。之所以叫IDENTITY列，是由于其支持ANSI SQL 关键字 IDENTITY，其内部实现还是使用SEQUENCE。
不过推出这个新语法也是应该的，毕竟MyQL已经有 AUTO_INCREMENT列，而SQL Server也已经有IDENTITY列。

IEDNTITY列最主要用于surrogate key，也就是人造的唯一键。其语法链如下：

语法的文字表述如下：

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

GENERATED和AS IDENTITY是必需的。ALWAYS和BY DEFAULT的区别如下：

• ALWAYS
  如果指定 ALWAYS，则 Oracle Database 始终使用序列生成器为列分配值。如果尝试使用 INSERT 或 UPDATE 明确为列分配值，则将返回错误。这是默认设置。

• BY DEFAULT
  如果指定 BY DEFAULT，则 Oracle Database 默认使用序列生成器为列分配值，但您也可以明确为列分配指定的值。如果指定 ON NULL，则当后续 INSERT 语句尝试分配计算结果为 NULL 的值时，Oracle Database 将使用序列生成器为列分配值。

接下来再看identity_options，除了黄色的部分（START WITH LIMIT VALUE），其他参数和CREATE SEQUENCE语句完全相同。

START WITH LIMIT VALUE 是针只能与 ALTER TABLE MODIFY 一起使用。如果指定 START WITH LIMIT VALUE，则 Oracle 数据库将锁定表并查找表中的最大标识列值（对于增加序列）或最小标识列值（对于减少序列），并将该值指定为序列生成器的高水位标记。序列生成器返回的下一个值将是高水位标记 + INCREMENT BY 整数（对于增加序列）或高水位标记 - INCREMENT BY 整数（对于减少序列）。

上面的语法说明有点拗口，简单来说，如果是递增值为1的序列，使用START WITH LIMIT VALUE，下一个值就是1。

这篇文章Matching the start-with value of an identity column with table data很好的解释了的用途。文章中设定了如下的场景，每次ETL之前，需要清空表，然后插入数据，插入的数据希望surrogate key每次都从1开始，这就是通过START WITH LIMIT VALUE实现的。其中使用到的SQL如下：

-- 创建表
CREATE TABLE staging_t (id NUMBERGENERATED BY DEFAULT ON NULL AS IDENTITY ( CACHE 100 ),c1 VARCHAR2(100),c2 DATE
);-- ETL插入数据（本例插入10条数据）
truncte table staging_t;
insert into staging_t (c1,c2
)
with lots_of_ints as (
selectrownum rn
fromdual
connect byrownum  <= 10)
selectrn,sysdate - rn / 1440
fromlots_of_ints;-- 查看数据
select * from staging_t;-- 修改IDENTITY列属性
alter table staging_t modifyid generated by default on nullas identity (start with limit value);-- ETL插入数据（SQL同前）
-- 查看数据（SQL同前）

另外几个需要强调的：

• CACHE integer指定数据库预先分配并保留在内存中的序列值的数量以便更快地访问。如果您在 Oracle Real Application Clusters 环境中使用序列，Oracle 建议使用 CACHE 设置来增强性能。
• MAXVALUE和MINVALUE。对于正值，此整数值可以有 28 位或更少的数字，对于负值，此整数值可以有 27 位或更少的数字。简单来说，最大值为10²⁸-1；最小值为-(10²⁷ -1)。理论上，这个最小和最大值都是无法达到的。

参考

• How to create id with AUTO_INCREMENT on Oracle?
• Oracle Identity Column With Examples
• Oracle Database 19c SQL Language Reference
• Identity Columns in Oracle Database 12c Release 1 (12.1)
• Oracle IDENTITY column versus PRIMARY KEY