Oracle 12c 新特性 --- 标识列

1概念说明

Table columns have been enhanced to support the American National Standards Institute (ANSI) SQL keyword IDENTITY.
This provides a standards based approach to the declaration of automatically incrementing columns simplifying application development and making the migration of DDL to Oracle simpler.

表列已经增强，以支持美国国家标准协会(ANSI)SQL关键字标识。
这提供了一种基于标准的方法，以自动递增的列来简化应用程序开发，并使DDL迁移到Oracle更简单。

Oracle Database 12c Release 1 implements ANSI-compliant IDENTITY columns. Migration from database systems that use identity columns is simplified and can take advantage of this new functionality.

This feature implements auto increment by enhancing DEFAULT or DEFAULT ON NULL semantics for use by SEQUENCE.NEXTVAL and SYS_GUID, supports built-in functions and implicit return of default values.

创建语法：
GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

identity_clause

Use this clause to specify an identity column. The identity column will be assigned an increasing or decreasing integer value from a sequence generator for each subsequent INSERT statement. You can use the identity_options clause to configure the sequence generator.

ALWAYS 
If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned. This is the default.

BY DEFAULT 
If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign a value to the column by default, but you can also explicitly assign a specified value to the column. If you specify ON NULL, then Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

identity_options

Use the identity_options clause to configure the sequence generator. The identity_options clause has the same parameters as the CREATE SEQUENCE statement. Refer to CREATE SEQUENCE for a full description of these parameters and characteristics. The exception is START WITH LIMIT VALUE, which is specific to identity_options and can only be used with ALTER TABLE MODIFY. Refer to identity_options for more information.

Note:
When you create an identity column, Oracle recommends that you specify the CACHE clause with a value higher than the default of 20 to enhance performance.

 

2 实验 

--用户要有CREATE SEQUENCE 权限，否则提示ORA-01031: insufficient privileges"错误
SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SESSION SET CONTAINER=pdbcndba;

Session altered.

SQL> GRANT CREATE TABLE, CREATE SEQUENCE TO cndba;

Grant succeeded.

SQL> CONN cndba/cndba@pdbcndba
Connected.
--当强制使用 ALWAYS 时，null和指定值时都会报错，原因是当使用ALWAYS时，字段值只能使用序列
SQL> DROP TABLE cndba PURGE;

Table dropped.

SQL> CREATE TABLE cndba (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(30)
);  2    3    4  

Table created.

SQL> INSERT INTO cndba (description) VALUES ('Just DESCRIPTION');  
  2  
SQL> INSERT INTO cndba (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO cndba (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO cndba (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
                   *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL> INSERT INTO cndba (id, description) VALUES (999, 'ID=999 and DESCRIPTION');
INSERT INTO cndba (id, description) VALUES (999, 'ID=999 and DESCRIPTION')
                   *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

--当使用BY DEFAULT 时，指定null值时会提示错误，因为标识列总是不为空的。 SQL> DROP TABLE cndba PURGE;

Table dropped.

SQL> CREATE TABLE cndba (
  id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
  description VARCHAR2(30)
);  2    3    4  

Table created.

SQL> INSERT INTO cndba (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO cndba (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

SQL> INSERT INTO cndba (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');
INSERT INTO cndba (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION')
                                            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("CNDBA"."CNDBA"."ID")

--当使用 BY DEFAULT ON NULL时，null 值时可以指定的。 SQL> DROP TABLE cndba PURGE;

Table dropped.

SQL> CREATE TABLE cndba (
  id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  description VARCHAR2(30)
);  2    3    4  

Table created.

SQL> INSERT INTO cndba (description) VALUES ('Just DESCRIPTION');

1 row created.

SQL> INSERT INTO cndba (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

1 row created.

SQL> INSERT INTO cndba (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

1 row created.

SQL> SELECT * FROM cndba;

	ID DESCRIPTION
---------- ------------------------------
	 1 Just DESCRIPTION
       999 ID=999 and DESCRIPTION
	 2 ID=NULL and DESCRIPTION

--基于创建序列特权的需求，一个序列被用来填充标识列。 SQL> ALTER SESSION SET CONTAINER=pdbcndba;

Session altered.
SQL>  COLUMN object_name FORMAT A20
SQL> SELECT object_name, object_type
FROM   user_objects;  2  

OBJECT_NAME	     OBJECT_TYPE
-------------------- -----------------------
T1		     TABLE
ISEQ$$_79359	     SEQUENCE
ISEQ$$_79371	     SEQUENCE
CNDBA		     TABLE

--[DBA|ALL|USER]_TAB_IDENTITY_COLS 表中查看identity columns信息 SQL> SET LINESIZE 100
SQL> COLUMN table_name FORMAT A20 
SQL> COLUMN column_name FORMAT A15
SQL> COLUMN generation_type FORMAT A10
SQL> COLUMN identity_options FORMAT A50
SQL> SELECT table_name, 
       column_name,
       generation_type,
       identity_options
FROM   all_tab_identity_cols
WHERE  owner = 'CNDBA'
ORDER BY 1, 2;  2    3    4    5    6    7  

TABLE_NAME	     COLUMN_NAME     GENERATION IDENTITY_OPTIONS
-------------------- --------------- ---------- --------------------------------------------------
CNDBA		     ID 	     BY DEFAULT START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 9999999
						999999999999999999999, MIN_VALUE: 1, CYCLE_FLAG: N
						, CACHE_SIZE: 20, ORDER_FLAG: N
						

--表和序列之间的链接存储在SYS.IDNSEQ$表中 SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SESSION SET CONTAINER=pdbcndba;

Session altered.

SQL> COLUMN sequence_name FORMAT A30                               
SQL> SELECT a.name AS table_name,
       b.name AS sequence_name
FROM   sys.idnseq$ c
       JOIN sys.obj$ a ON c.obj# = a.obj#
       JOIN sys.obj$ b ON c.seqobj# = b.obj#;  2    3    4    5  

TABLE_NAME	     SEQUENCE_NAME
-------------------- ------------------------------
T1		     ISEQ$$_79357
T1		     ISEQ$$_79359
CNDBA		     ISEQ$$_79371

--在执行计划值中可以看到序列被使用
SQL> SET AUTOTRACE ON
SQL> SET LINESIZE 200
SQL> INSERT INTO cndba (description) VALUES ('Just DESCRIPTION');

1 row created.


Execution Plan
----------------------------------------------------------
Plan hash value: 372728000

-----------------------------------------------------------------------------------
| Id  | Operation		 | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
-----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT	 |	  |	1 |   100 |	1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | CNDBA  |	  |	  |	       |	  |
|   2 |   SEQUENCE		 | CN_SEQ |	  |	  |	       |	  |
-----------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
	 58  recursive calls
	  6  db block gets
	 56  consistent gets
	  0  physical reads
	572  redo size
	848  bytes sent via SQL*Net to client
	979  bytes received via SQL*Net from client
	  3  SQL*Net roundtrips to/from client
	  6  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

参考链接:

http://docs.oracle.com/database/121/DRDAA/migr_tools_feat.htm#DRDAA109

https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1