文档章节

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

小强斋太
 小强斋太
发布于 2016/11/09 20:06
字数 1479
阅读 95
收藏 0

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

clip_image001

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

clip_image002

更新测试:

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

结论:

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

clip_image0021

插入测试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;

clip_image003

插入测试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")

clip_image003

更新测试:

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

     结论:

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

clip_image004

更新测试

UPDATE IDENTITY_TEST_TAB SET ID=3 WHERE ID=1

[SQL]UPDATE IDENTITY_TEST_TAB SET ID=3 WHERE ID=1

受影响的行: 1

时间: 0.004s

     结论:

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

clip_image005

发现建表的时候自动生成了一个sequence

SELECT table_name, 

column_name,

generation_type,

identity_options

FROM all_tab_identity_cols

WHERE owner = 'TEST_USER';

clip_image006

表和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';

clip_image007

3.2 GENERATED IDENTITY 中sequence不能单独被删除

DROP TABLE IDENTITY_TEST_TAB

删除表后,该sequence还存在。且该sequence无法被删除

clip_image008

必须

purge table IDENTITY_TEST_TAB,

结论:

  1. Identity Columns 是基于序列实现的
  2. GENERATED IDENTITY 中sequence不能单独被删除
  3. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除

3.3 执行插入语句时的解释计划

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

clip_image009

 对比发现:该方式的效率比触发器+序列的方式高!

四、identity_options

clip_image010

查看表的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自增长列功能

Identity Columns in Oracle Database 12c Release 1 (12.1)

Database SQL Language Reference

本文转载自:http://www.cnblogs.com/xqzt/p/4455149.html

共有 人打赏支持
小强斋太
粉丝 0
博文 181
码字总数 0
作品 0
广州
私信 提问
《Oracle PL/SQL开发指南》学习笔记16——Oracle PL/SQL Development Overview (Review Section,Mastery Check)

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hpdlzu80100/article/details/84451754 这章学的有点尴尬,基本上是看不懂啊! 本来觉得对Oracle有点熟悉了,...

预见未来to50
11/24
0
0
Oracle Database 12c新特性汇总页面

clean Liu关于DB 12c新特性的研究文章如下: 【Oracle Database 12c新特性】 In-Database Archiving数据库内归档 【Oracle Database 12c新特性】SYSAUTOSPMEVOLVETASK 自动作业 【Oracle Da...

科技小能手
2017/11/12
0
0
Oracle 10g Conenct to 12.2 with ora-28040

Oracle 10g Conenct to 12.2 with ora-28040 love wife & love life --Roger2017-07-0849 阅读 databaseoracle 本站文章除注明转载外,均为本站原创: 转载自 love wife & love life —Roger......

love wife & love life --Roger
2017/07/08
0
0
oracle 12.1的那些坑

1 数据库不重启也会把一些启动信息打在日志上,让你怀疑这库是不是无厘头重启,最重要的是没有其他的相关日志,让你怀疑是不是不要干DBA这行了; 1.1禁止参数信息dump至Alert日志中 logsegme...

snowhill
06/29
0
0
【故障处理】ORA-28040: No matching authentication protocol

【故障处理】ORA-28040: No matching authentication protocol 1.1 BLOG文档结构图 1.2 前言部分 1.2.1 导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其...

小麦苗
2016/12/25
324
0

没有更多内容

加载失败,请刷新页面

加载更多

Apache Sentry架构介绍

cdh版本的hadoop在对数据安全上的处理通常采用Kerberos+Sentry的结构。 kerberos主要负责平台用户的权限管理,sentry则负责数据的权限管理。 下面我们来依次了解一下: Kerberos包含一个中心...

hblt-j
23分钟前
0
0
First Bad Version(leetcode278)

You are a product manager and currently leading a team to develop a new product. Unfortunately, the latest version of your product fails the quality check. Since each version is......

woshixin
29分钟前
1
0
executor 和task 优先于线程(68)

java.util.concurrent 包里有一个Executor 框架 基于接口的任务执行工具 只需要一行代码 提交一个runnable 方法 优雅的终止(必须做到,不然虚拟机可能不会退出) 对于负载不重的服务 Execut...

Java搬砖工程师
29分钟前
1
0
一条SQL查询语句是如何执行的

123

writeademo
32分钟前
3
0
CSS中position属性( absolute | relative | static | fixed )详解

四个属性的特点 static:无特殊定位,对象遵循正常文档流。top,right,bottom,left等属性不会被应用。 relative:对象遵循正常文档流,但将依据top,right,bottom,left等属性在正常文档流...

简心
37分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部