oracle 在上一条数据的基础上递增插入_由点及面了解Oracle的Sequence序列

点击标题下「蓝色微信名」可快速关注

预计阅读时间:22分钟

765e7fdb1db805ce50b205f0eab8777f.png

sequence作为Oracle一个非常普通的对象,在实际设计和开发的过程中,还是有些知识,以及细节,值得总结和注意,下面我会从四个方面,展开介绍,

1. sequence基本介绍

2. sequence性能问题

3. 18c的sequence新特性

4. 一些开发中sequence的使用经验

1. sequence基本介绍

下图是11g的《Reference》,对于create sequence语法的介绍,

93f6e76f327aaf2e04978176fd100c8c.png

可以知道,sequence有一系列参数,可以辅助序列的创建,实现各种需求,

INCREMENT BY:步长,不能为0,正值最大28位,表示升序,负值最大27位,表示降序。该参数的绝对值,必须小于MAXVALUE和MINVALUE之差。默认值为1。
START WITH:起始值,对于降序序列,默认值为序列的最大值,对于升序序列,默认值为序列的最小值。
MAXVALUE:最大值,正值最大28位,负值最大27位,MAXVALUE >= START WITH,MINVALUE > MAXVALUE。
NOMAXVALUE:对于升序序列,最⼤值为1028-1,对于降序序列,最⼤值为-1,该参数为默认值。
MINVALUE:最小值,正值最大28位,负值最大27位,MINVALUE <= START WITH,MINVALUE < MAXVALUE。
NOMINVALUE:对于升序序列,最小值为1,对于降序序列,最小值为-(1027 -1),该参数为默认值。
CYCLE:表示序列值到达最⼤或最⼩值后继续循环生成新值。
NOCYCLE:表示序列值到达最大或最小值后不会生成新值,该参数为默认值。
CACHE:表示在内存中缓存多少个序列值,最大28位,最小值为2,对于CYCLE=Y的序列,CACHE的值必须小于循环的序列值,CACHE允许的最大值必须小于如下公式:CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)如果数据库崩溃,还未提交的缓存序列,就会丢失。RAC下建议使用cache选项。
NOCACHE:不缓存序列值,如果不指定CACHE和NOCACHE,默认缓存20个的序列值。
ORDER:表示序列会按照请求的顺序,生成序列值,如果使用序列,作为时间戳,则此参数有用,但若作为主键,未必需要保证序列的顺序。如果用的RAC,ORDER是唯一可以保证按序创建序列值的方法,除此之外,序列都是按序产生的。
NOORDER:不需要保证序列按序创建,这是默认配置。

通常我们创建一个序列,会包含这些常用的参数,

1e1314b3ef064610ac26d5490a85b289.png

要了解sequence背后,Oracle做了什么,可以执行10046事件,例如执行create sequence bisal_seq语句,10046的trace记录如下,会向seq$插入记录,从字段名称可以看出,存储的是sequence相应的参数值,

c2fc20094149b9e122cddb18f1b72053.png

同样检索seq$,可以和上述trace对应起来,

c538c4fcb57143227d4d24dd1db6211c.png

同时,会向obj$插入一条对象的记录,标记序列对象,

a53332555094956501d407d0a7fb2ecf.png

执行检索select bisal_seq.currval, bisal_seq.nextval from dual语句,会更新seq$,主要更新highwater高水位,因为序列要保证值唯一,

227c6505e04ba2b711295b6068ceab89.png

创建序列,不带任何参数,默认参数值如下,

42006ff4336f16a90b68f40351ecc320.png

问题1:cache存储的是什么?

有些人可能认为存储的是1,2,3...20,但实际存储的是目标值,例如20,其他值存储在缓存中。

问题2:缓存在什么位置?

序列值是基于会话读取的,但并不是存储在会话中,而是存在SGA。

语法上,会话中首先要nextval,否则直接currval,会提示错误,使用nextval取出下一值,就可以用currval查看当前值了,

96755aa3f02201aae0fabdc13dd31ef6.png

2. sequence性能问题

sequence是非常普通的Oracle对象,但如果使用不当,就可能会导致一些性能问题,如下介绍了三个场景。

场景1:RAC下,nocache选项创建频繁使用的sequence

从之前介绍中,我们了解了,当需要读取的sequence值,到达了当前cache的最大值,就会更新seq$的highwater,这样做的目的,就是为了保证序列值唯一。如果创建序列,不使用cache选项,相当于每次使用序列,都要更新seq$表。如果使用的是RAC,每个节点使用了序列,都要更新seq$,可能出现的场景,就是seq$表的数据块,会在实例之间频繁地传输,进而就可能产生一些gc相关的等待事件,造成性能问题。因此RAC下,对于频繁使用的序列,用nocache选项非常危险。

场景2:RAC下,order选项创建频繁使用的sequence

创建序列,可以采用order选项,为了可以按顺序产生序列值,在RAC下,由于存在多实例,为了保证不同节点间,序列产生的值是连续的,会使用特殊的全局锁(SV)来控制,序列当前值就是通过这个锁的流转来传送,实现跨实例串行化生成序列值,频繁使用序列,就可能会出现DFS lock handle、latch: gets resource hash list、row cache lock等待事件,造成性能问题,因此尤其对于RAC,用order选项创建频繁使用的sequence非常危险。

场景3:sequence作为主键或者唯一键

无论sequence无论作为主键还是唯一键,都会自动创建一个唯一索引,由于他的值是递增产生的,因此总会发生索引单向增长,如下图所示,对于递增的序列,总会在B树索引最右侧的索引块,插入新值,当数据块没有空间的时候,就会发生9-1分裂,创建新的数据块,因此争用总会发生在索引叶子节点的最右侧数据块上,

c233a360c8702d9f3b3477063af64a06.png

其实针对这问题,之前RWP的Andrew就有介绍,另外,我们系统设计初期的POC,碰见了相同的问题,有三种解决方案。

方案一:将索引重建为reverse-key index

这种方案,可以缓解索引热块的争用,但是随着数据量的增加,索引越大,对于范围检索,一次检索可能需要读取到buffer cache的索引数据块就会越多,一方面可能会产生磁盘IO方面的等待,另一方面可能会将其他表或索引的数据挤出内存,因此,很有可能只是从索引争用,转换成另一种资源的等待,没有从根本解决这个问题。

方案二:将索引重建为hash partition index

如果是单实例,这种方案会有效,因为他将原先争用的块数据,分散到了不同的数据块,但是,如果迁移RAC,由于频繁的使用,可能会出现索引数据块在节点间频繁的传输,而且随着节点数增加,传输的可能性就会越大,还是会产生性能的问题。

方案三:编码生成的智能主键

其实,我们所要解决的,就是这三个问题,

问题1:避免实例间传输

问题2:避免索引单向争用

问题3:保证序列取值不重复

针对问题1,我们选择实例号,作为序列的开始,保证数据插入,会保存在节点的一边,

bdeeb62d0a3b1aae59a2f1f6b6fb1d17.png

针对问题2,我们选择进程号取余,将索引的维护分散到同⼀实例的多个内存块上,

7b1b0216b727a65b8fd3081f7dabde02.png

针对问题3,我们选择sequence,保证唯一,

1fd77f879a039c8c7633a8ed213815b0.png

因此,我们的智能主键,算法如下,

4b72d06d278125fc01d010396740b6e3.png

又或者可以这样,

cf30af078a9eea7212ff4e7ac1ce621e.png

虽然SQL上略有区别,但是中心思想,是一致的,就是根据实例号、进程号、以及序列值,拼接出能避免实例间传输、避免索引单向的争用、以及保证唯一的主键值。

3. 18c的sequence新特性

上一节案例三,作为RWP的经典案例,智能主键的解决方案,已经整合进了Oracle 18c数据库层面,这个功能就是Scalable序列,即可伸缩序列,这个功能在12.2就已经引入,但是作为隐藏的功能,在18c中,才正式推广出来。

语法很简单,多了SCALE/NOSCALE/EXTEND/NOEXTEND这些参数,

CREATE | ALTER SEQUENCE sequence_name

SCALE [EXTEND | NOEXTEND] | NOSCALE

当SCALE语句被指定时,一个6位数的数字被指定作为序列的前缀,末尾是正常的序列数字,两者联合成为新的序列:

scalable sequence number = 6 digit scalable sequence offset number  
|| normal sequence number

参数介绍如下,

db4b3b4767c389441ef2a8e53a2be1c6.png

指定了SCALE,偏移量的算法是:

(instance_id % 100)(_kqdsn_instance_digits) + 100|| (session_id % 1000)(_kqdsn_cpu_digits)|| seq(EXTEND/NOEXTEND确定是否固定宽度)

其中隐藏参数_kqdsn_instance_digits和_kqdsn_cpu_digits,可以在会话级和实例级调整,默认这两个参数值为2和3。

EXTEND表示序列总长度=[X个数字+Y个数字],X默认值是6位数,Y是MAXVALUE指定的位数。对于EXTEND来说MAXVALUE代表的后面正常序列的长度,而不是可伸缩序列的总长度。

NOEXTEND(SCALE默认值)

表示序列总长度不能超过MAXVALUE定义的长度,由于前面默认是6位数+

正常的序列号,所以长度最少是7位数。对于NOEXTED来说MAXVALUE

代表的是可伸缩序列的总长度。

如果默认,会采用NOEXTEND,定义6位长度,则会提示错误,因为前面6位已经占满了,要么增加一位,要么改为EXTEND,

85f1210935999e6cc02a96d52fb22caa.png

NOEXTEND,指定7位,则可以创建,

0ec2b8cc7ae3fcb8c88a87f23840d7b7.png

如果指定了EXTEND,则只需要指定序列的长度,

38fe6dd93ae137ff8c53a93cee5e5352.png

可以看出,使用SCALE参数,就可以实现原来编码才能实现的智能主键的功能,由于值分散开了,因此会降低索引的争用,从数据库层面,解决了实例间的传输、索引单向的争用、以及保证唯一的主键值。

4. 一些开发中sequence的使用经验

在开发中对于sequence的使用,会有一些技巧和经验,下面介绍两个我们日常开发可能碰见的需求场景,

第一个场景案例,是如何使用jdbc读取新插入Oracle的sequence值,这个案例来自于斗佛博客的介绍(https://blog.csdn.net/yzsind/article/details/6918506,版权归属斗佛),提出了五种读取sequence值的方法,另外,也可以参考历史文章《JDBC读取新插入Oracle数据库Sequence值的方法》。

首先,作为准备工作,如下是得到数据库连接的公共代码,

public Connection getConnection() throws Exception{
  Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
  Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:dbname", "username", "password");return conn;
}

方法一:
1. 先用select seq_t1.nextval as id from dual取到新的sequence值。
2. 然后将最新的值通过变量传递给插入的语句:insert into t1(id) values(?) 
3. 最后返回开始取到的sequence值。

这种方法的优点很明显,就是代码简单直观,易理解,使用的人也最多,缺点是需要两次sql交互,性能不佳。

public int insertDataReturnKeyByGetNextVal() throws Exception {
   Connection conn = getConnection();
   String vsql = "select seq_t1.nextval as id from dual";
   PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql);
   ResultSet rs=pstmt.executeQuery();
   rs.next();int id=rs.getInt(1);
   rs.close();
   pstmt.close();
   vsql="insert into t1(id) values(?)";
   pstmt =(PreparedStatement)conn.prepareStatement(vsql);
   pstmt.setInt(1, id);
   pstmt.executeUpdate();
   System.out.print("id:"+id);return id;
}

方法二:
1. 先用insert into t1(id) values(seq_t1.nextval)插入数据。
2. 然后使用select seq_t1.currval as id from dual返回刚才插入的记录生成的sequence值。

注:seq_t1.currval表示取出当前会话的最后生成的sequence值,由于是用会话隔离,只要保证两个SQL使用同一个Connection即可,对于采用连接池应用需要将两个SQL放在同一个事务内才可保证并发安全。另外如果会话没有生成过sequence值,使用seq_t1.currval语法会报错。

这种方法的优点,可以在插入记录后返回sequence,适合于数据插入业务逻辑不好改造的业务代码,缺点是需要两次sql交互,性能不佳,并且容易产生并发安全问题。

public int insertDataReturnKeyByGetCurrVal() throws Exception {
   Connection conn = getConnection();
   String vsql = "insert into t1(id) values(seq_t1.nextval)";
   PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql);
   pstmt.executeUpdate();
   pstmt.close();
   vsql="select seq_t1.currval as id from dual";
   pstmt =(PreparedStatement)conn.prepareStatement(vsql);
   ResultSet rs=pstmt.executeQuery();
   rs.next();int id=rs.getInt(1);
   rs.close();
   pstmt.close();
   System.out.print("id:"+id);return id;
}

方法三:

采用pl/sql的returning into语法,可以用CallableStatement对象设置registerOutParameter取得输出变量的值。

这种方法的优点,是只要一次sql交互,性能较好,缺点是需要采用PL/SQL语法,代码不直观,使用较少。

public int insertDataReturnKeyByPlsql() throws Exception {
   Connection conn = getConnection();
   String vsql = "begin insert into t1(id) values(seq_t1.nextval) returning id into :1;end;";
   CallableStatement cstmt =(CallableStatement)conn.prepareCall (vsql); 
   cstmt.registerOutParameter(1, Types.BIGINT);
   cstmt.execute();int id=cstmt.getInt(1);
   System.out.print("id:"+id);
   cstmt.close();return id;
}

方法四:
采用PreparedStatement的getGeneratedKeys方法,conn.prepareStatement的第二个参数可以设置GeneratedKeys的字段名列表,变量类型是一个字符串数组。

注:对Oracle数据库这里不能像其它数据库那样用prepareStatement(vsql,Statement.RETURN_GENERATED_KEYS)方法,这种语法是用来取自增类型的数据。Oracle没有自增类型,全部采用的是sequence实现,如果传Statement.RETURN_GENERATED_KEYS则返回的是新插入记录的ROWID,并不是我们相要的sequence值。

这种方法的优点,是性能良好,只要一次sql交互,实际上内部也是将sql转换成oracle的returning into的语法,缺点是只有Oracle10g才支持,使用较少。

public int insertDataReturnKeyByGeneratedKeys() throws Exception {
   Connection conn = getConnection();
   String vsql = "insert into t1(id) values(seq_t1.nextval)";
   PreparedStatement pstmt =(PreparedStatement)conn.prepareStatement(vsql,new String[]{"ID"});
   pstmt.executeUpdate();
   ResultSet rs=pstmt.getGeneratedKeys();
   rs.next();int id=rs.getInt(1);
   rs.close();
   pstmt.close();
   System.out.print("id:"+id);return id;
}

方法五:
和方法三类似,采用oracle特有的returning into语法,设置输出参数,但是不同的地方是采用OraclePreparedStatement对象,因为jdbc规范里标准的PreparedStatement对象是不能设置输出类型参数。

最后用getReturnResultSet取到新插入的sequence值,这种方法的优点,是性能最好,因为只要一次sql交互,oracle 9i也支持,缺点是只能使用Oracle jdbc特有的OraclePreparedStatement对象。

public int insertDataReturnKeyByReturnInto() throws Exception {
   Connection conn = getConnection();
   String vsql = "insert into t1(id) values(seq_t1.nextval) returning id into :1";
   OraclePreparedStatement pstmt =(OraclePreparedStatement)conn.prepareStatement(vsql);
   pstmt.registerReturnParameter(1, Types.BIGINT);
   pstmt.executeUpdate();
   ResultSet rs=pstmt.getReturnResultSet();
   rs.next();int id=rs.getInt(1);
   rs.close();
   pstmt.close();
   System.out.print("id:"+id);return id;
}

如下是五种方法的汇总,

a3361cb20a647bf56a15835781b584bf.png

除了方案4,由于其只支持10g,我实际测试了其它几个方案,均为一次读取1000个序列值,经过测试,方法1和方法2,都因为两次交互,时间较长,方案3和方案5,由于一次交互,时间比较短,但是从可读性上,方案5要优于方案3,具体需要选择何种方案,就是实际的需求场景了,

4136b99c5997614807599ef4a4097a91.png

第二个场景案例,在代码中,是如何一次性读取多个sequence值,例如假设场景,需要一次性取出5个sequence值,如何操作?

第一种方法,很直观的想法,就是for循环,一次读取一个值,循环5次,这种方法很简单,但是不高效,因为他需要和数据库交互5次,

for (int i = 0; i 5; i++) {
   select seq.nextval from dual;
}

第二种方法,借助另一张表,实现次数的轮询,这种方法,仅需要交互一次,但是取决于all_objects(或者任意其他表)的数据量,比如只有1行,即使使用rownum<5,也没有作用,另一方面,如果这个序列,被频繁地使用,很有可能让这张表成为热表,

select seq.nextval from 
(select 1 from all_objects where rownum <= 5);

上述两种方法,各有各的缺点,第三种方法,使用了伪表,并通过层次查询connect by,实现了一次读取多个值,只交互了一次,并且没有借助外部表,相对来说,这种方法,更值得推荐,

select seq.nextval from dual connect by rownum <= 5;

sequence虽然普通,但蕴含的知识,一点不少,记得eygle曾经说过,学习Oracle的一种方法,就是由点及面,其实对于任何知识的学习,这个方法都适用,通过上面对sequence各种问题的学习,充分了解、深入了解,才能让我们更好地使用他,不能仅限于“会用”的层面,这样才能不断地成长,才能遇见更好的自己。

转文至此。

63f7286b9d5563d1ad45be0de2415067.png

关注个人微信公众号“一森咖记” c64b5c2f213cebb3dc55685fcd023656.png 1ec18f3ff1fb49e87626a9a7533dc7ba.png

   

近期热文

你可能也会对以下话题感兴趣。点击链接便可查看。

  • Oracle ADG同步技术,DBA必备的一种“后悔药”

  • 神技_如何快捷下载Oracle补丁的方法?!

  • 趋势篇:oracle 11g,12c,18c,19c之support lifetime

  • Configuring Kernel Parameters about SHMMAX on HP

  • 对recursive calls的深刻理解

  • Centos能不能替换RHEL?

  • RAC1 服务器要重启,喂:RAC2你先顶一下,咋搞?!

  • 对recursive calls的深刻理解

  • OpenJDK和Oracle JDK有什么区别和联系?

  • Centos能不能替换RHEL?

  • PLSQL Developer中控制结果集表格自动提交

  • alter table T1 add column新增字段执行时间超长,咋回事?

  • Oracle 11g 异机rman恢复报错ORA-27302:failure occurred at: sskgpcreates

  • 年末总结_聊一聊数据库行业的“继往开来”

  • Materialized view物化视图的一个简单应用场景

  • 干货:RHEL7.2生产环境下双节点12c RAC搭建实操

  • 【干货篇】在国内外数据库百家争鸣的时代,DBA们该何去何从?

  • LINUX环境:MySQL和Oracle开机自启动,咋搞?Logminer:oracle人为误操作之恢复神器

  • What:ASM自动脱落了

  • 实操:12C RAC环境下的ADG同步库搭建

  • “神器”:Oracle日志采集分析工具——TFA

  • Oracle Rac:关闭透明大页的原因及方法

  • 实操篇:Oracle 19c的安装部署

  • 浅谈MySQL三种锁:全局锁、表锁和行锁

  • Oracle如何访问MySql:透明网关

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值