文档章节

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

小强斋太
 小强斋太
发布于 2016/11/09 20:06
字数 1479
阅读 80
收藏 0
点赞 0
评论 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 Database 12c新特性汇总页面

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

科技小能手 ⋅ 2017/11/12 ⋅ 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

解读 Oracle 12c 的 12 个新特性

原文出处:askmaclean 在OOW 2012上Tom kyte介绍了Oracle新一代重量级数据库产品12c 的12个新特性, 目前Open World 2012的主要PDF都可以下载了,传送门在此:Search Content Catalog for Or...

红薯 ⋅ 2012/10/10 ⋅ 22

【故障处理】ORA-28040: No matching authentication protocol

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

小麦苗 ⋅ 2016/12/25 ⋅ 0

oracle 12.1的那些坑

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

snowhill ⋅ 2017/10/17 ⋅ 0

甲骨文周二将发布季度补丁 涉及 47 个产品

作为习惯,甲骨文将会在这个“补丁周二”发布它们的2014年1月份的季度补丁,和微软同一 天。即将发布的更新会影响47个产品。不过147个安全修复中,一些漏洞会影响多个产品,因此实际涉及的安...

oschina ⋅ 2014/01/14 ⋅ 21

Oracle 12cR1 RAC 在VMware Workstation上安装(下)—静默安装

Oracle 12cR1 RAC 在VMware Workstation上安装(下)—静默安装 1.1 静默安装 1.1.1 静默安装grid 安装之前使用脚本进行校验,确保所有的failed选项都可以忽略。 1.1.1.1 新建/etc/oraInst.lo...

lhrbest ⋅ 2017/01/21 ⋅ 0

添加Scott用户

C:UsersAdministrator>echo %ORACLE_HOME%C:appAdministratorvirtualproduct12.2.0dbhome_2C:UsersAdministrator>sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on 星期六 ......

whshurk ⋅ 2017/12/16 ⋅ 0

12c静默安装

/usr/sbin/groupadd -g 501 oinstall /usr/sbin/groupadd -g 502 dba /usr/sbin/groupadd -g 504 asmadmin /usr/sbin/groupadd -g 506 asmdba /usr/sbin/groupadd -g 507 asmoper /usr/sbin/......

yangwangbaby ⋅ 2017/06/24 ⋅ 0

利用Docker快速部署Oracle环境

工作中需要频繁使用Oracle环境,但是每次搭建起来比较消耗时间,本想通过虚拟机模板的方式来快速安装oracle vm,但是每次改ip等环境也很耗时,因此想到docker中有没有已经做好的images,这样...

taojin1240 ⋅ 2017/03/13 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

DevOps 资讯 | PostgreSQL 的时代到来了吗 ?

PostgreSQL是对象-关系型数据库,BSD 许可证。拼读为"post-gress-Q-L"。 作者: Tony Baer 原文: Has the time finally come for PostgreSQL?(有删节) 近30年来 PostgreSQL 无疑是您从未听...

RiboseYim ⋅ 9分钟前 ⋅ 0

Cube、Cuboid 和 Cube Segment

1.Cube (或Data Cube),即数据立方体,是一种常用于数据分析与索引的技术;它可以对原始数据建立多维度索引。通过 Cube 对数据进行分析,可以大大加快数据的查询效率 2.Cuboid 在 Kylin 中特...

无精疯 ⋅ 47分钟前 ⋅ 0

github太慢

1:用浏览器访问 IPAddress.com or http://tool.chinaz.com 使用 IP Lookup 工具获得github.com和github.global.ssl.fastly.net域名的ip地址 2:/etc/hosts文件中添加如下格式(IP最好自己查一...

whoisliang ⋅ 49分钟前 ⋅ 0

非阻塞同步之 CAS

为解决线程安全问题,互斥同步相当于以时间换空间。多线程情况下,只有一个线程可以访问同步代码。这种同步也叫阻塞同步(Blocking Synchronization). 这种同步属于一种悲观并发策略。认为只...

长安一梦 ⋅ 今天 ⋅ 0

云计算的选择悖论如何对待?

人们都希望在工作和生活中有所选择。但心理学家的调查研究表明,在多种选项中进行选择并不一定会使人们更快乐,甚至不会产生更好的决策。心理学家Barry Schwartz称之为“选择悖论”。云计算为...

linux-tao ⋅ 今天 ⋅ 0

Redis 注册为 Windows 服务

Redis 注册为 Windows 服务 redis 注册为 windows 服务相关命令 注册服务 redis-server.exe –service-install redis.windows.conf 删除服务 redis-server –service-uninstall 启动服务 re......

Os_yxguang ⋅ 今天 ⋅ 0

世界那么大,语言那么多,为什么选择Micropython,它的优势在哪?

最近国内MicroPython风靡程序界,是什么原因导致它这么火呢?是因为他功能强大,遵循Mit协议开源么? 错!因为使用它真的是太舒服了!!! Micropython的由来,这得益于Damien George这位伟大...

bodasisiter ⋅ 今天 ⋅ 0

docker 清理总结

杀死所有正在运行的容器 docker kill $(docker ps -a -q) 删除所有已经停止的容器(docker rm没有加-f参数,运行中的容器不会删掉) docker rm $(docker ps -a -q) 删除所有未打 dangling 标...

vvx1024 ⋅ 今天 ⋅ 0

关于学习

以前学车的时候,教练说了这样的一句话:如果一个人坐在车上一直学,一直学,反而不如大家轮流着学。因为一个人一直学,就没有给自己留空间来反思和改进。而轮流着学的时候大家下来之后思考上...

mskk ⋅ 今天 ⋅ 0

压缩工具之gzip-bzip2-xz

win下常见压缩工具:rar zip 7z linux下常见压缩工具:zip gz bz2 xz tar.gz tar.bz2 tar.xz gzip 不支持目录压缩 gzip 1.txt #压缩。执行后1.txt消失,生成1.txt.gz压缩文件 gzip -d 1.txt....

ZHENG-JY ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部