Oracle 数据库 IDENTITY 列

IDENTITY列是Oracle数据库12c推出的新特性。之所以叫IDENTITY列，是由于其支持ANSI SQL 关键字 IDENTITY，其内部实现照旧使用SEQUENCE。
不外推出这个新语法也是应该的，毕竟MyQL已经有 AUTO_INCREMENT列，而SQL Server也已经有IDENTITY列。
IEDNTITY列最重要用于surrogate key，也就是人造的唯一键。其语法链如下：

语法的文字表述如下：

1. GENERATED
2. [ ALWAYS | BY DEFAULT [ ON NULL ] ]
3. 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如下：

1. -- 创建表
2. CREATE TABLE staging_t (
3.     id NUMBER
4.         GENERATED BY DEFAULT ON NULL AS IDENTITY ( CACHE 100 ),
5.     c1 VARCHAR2(100),
6.     c2 DATE
7. );
9. -- ETL插入数据（本例插入10条数据）
10. truncte table staging_t;
11. insert into staging_t (
12.   c1,
13.   c2
14. )
15. with lots_of_ints as (
16. select
17.   rownum rn
18. from
19.   dual
20. connect by
21.   rownum  <= 10)
22. select
23.   rn,
24.   sysdate - rn / 1440
25. from
26.   lots_of_ints;
28. -- 查看数据
29. select * from staging_t;
31. -- 修改IDENTITY列属性
32. alter table staging_t modify
33.   id generated by default on null
34.   as identity (start with limit value);
36. -- ETL插入数据（SQL同前）
37. -- 查看数据（SQL同前）

复制代码

另外几个必要强调的：

• CACHE integer指定数据库预先分配并保存在内存中的序列值的数量以便更快地访问。如果您在 Oracle Real Application Clusters 情况中使用序列，Oracle 建议使用 CACHE 设置来增强性能。
  
• MAXVALUE和MINVALUE。对于正值，此整数值可以有 28 位或更少的数字，对于负值，此整数值可以有 27 位或更少的数字。简单来说，最大值为1028-1；最小值为-(1027 -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
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。