`
zhanglu0223
  • 浏览: 22796 次
  • 性别: Icon_minigender_1
  • 来自: 合肥
社区版块
存档分类
最新评论

数据库性能优化3——Oracle SEQUENCE的概念、作用以及创建(使得并发插入主键唯一)

 
阅读更多

1. Oracle Sequence的概念

Oracle使用序列来生成唯一编号,而不是使用SQLServer所用的数据类型uniqueidentifier。无论是哪种情况,主要用途都是为主键列生成一系列唯一编号。与uniqueidentifier数据类型不同,序列是与将其用于主键值的一个或多个表无关的数据库对象。

Oracle序列是原子对象,并且是一致的。也就是说,一旦您访问一个序列号,Oracle将在处理下一个请求之前自动递增下一个编号,从而确保不会出现重复值。

2. Oracle Sequence的作用

sequence是用来在多用户环境下产生唯一整数的数据库对象。序列产生器顺序生成数字,它可用于自动生成主键值,并能协调多行或者多表的主键操作。没有sequence,顺序的值只能靠编写程序来生成。先找出最近产生的值然后自增长。这种方法要求一个事务级别的锁,这将导致在多用户并发操作的环境下,必须有人等待下一个主键值的产生。而且此方法很容易产生主键冲突的错误,如下图:
time a......trans1 begin.........................................................
|
取max value=5
|
time b......max value+1=6........trans2 begin.....................
| |
other actionmax value=5
| |
time c.....commit;...................max value+1=6................
|
commt(ora-00001)


如上图,事务2会报主键冲突的错误,而再刷新一下页面(再执行一边程序),可能就正常了。
还有一个问题,那就是完成生成主键的程序(一般情况包含plsql块)本身对于并发调用也是一个瓶颈,因为这样的程序段往往是提供给好多程序去调用,如果代码端写的不够优化(比如没有使用邦定变量等等),或者此代码段存在问题,那么它所影响的是系统的全局。我们应该提倡开发人员使用sequence。sequence消除了序列化问题,而且改善了应用的并发能力。


创建sequence


sequence的命名最重要的是要统一,命名规则是次要的。可以使用CREATESEQUENCE命令创建Oracle序列。该命令所带参数包括增量、起始值、最大值、循环和缓存。可使用NEXTVAL和CURRVAL关键字访问序列值。NEXTVAL返回序列中的下一个编号,而CURRVAL提供对当前值的访问。
CREATE SEQUENCE emp_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10

NOORDER;

大多数序列代码是不言自明的。NOCYCLE表示序列在达到最小值或最大值后将不再生成其他值。NOCACHE表示序列值在被请求之前不会进行分配;可使用预分配机制来改善性能。NOORDER表示在生成编号时,不能保证按照请求编号的顺序返回这些编号(order:保证序列号按请求顺序产生.如果你想以序列号作为timestamp(时间戳)类型的话,可以采用该选项.对于将序列用于生成主键来说,一般用noorder,约定顺序通常并不重要.)
这里需要重点说明的是cache参数,它是为了应对并发访问的。cache参数告诉oracle预先分配一个sequence numbers的集合,并且保留在内存中,以便sequence number能够被快速的访问。这个内存的大小就是cache所指定的大小,当多个用户同时访问一个sequence的时候,是在oracle SGA中读取sequence当前的合理数值,如果并发访问太大,cache的大小不够,那么就会产生sequence cache相关的等待(enq: SQ - contention),影响系统性能。
既然cache涉及到了内存,那么就会想到oracle实例恢复的问题。如果数据库shutdown abort,sequence会如何呢?当然会有问题,sequence number保存在内存里的但是没有被应用到表中的会丢失!

修改sequence


除了修改sequence的starting number,你什么都能改,如果想改starting number,只能先drop然后create。
ALTER SEQUENCE emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;
修改很有用,最典型的情况是“需要把sequence 的current value改大一点,避免程序报错!”。你就可以看看current value是多少,然后修改increment by 足够大的值,然后执行.nextval,最后别忘了再将increnent by改成原来的值,还要注意做这些工作的前提是当前没有人用此sequence。


使用 sequence


CURRVAL 和 NEXTVAL 能够在以下情况使用:
insert的values字句、select中的select列表、update中的set字句

CURRVAL 和 NEXTVAL 不能够在以下情况使用:
子查询、视图和实体化视图的查询、带distinct的select语句、带 group by和order by的select语句、带union或intersect或minus的select语句、select中的where字句、create table与alter table中的default值、check约束条件。


删除sequence


drop sequence seq_a;
当删除sequence后,对应它的同义词会被保留,但是引用时会报错。


oracle rac环境中的sequence


oracle为了在rac环境下为了sequence的一致性,使用了三种锁:row cache lock、SQ锁、SV锁。
row cache lock的目的是在sequence指定nocache的情况下调用sequence.nextval过程中保证序列的顺序性;
SQ锁是应用于指定了cache+noorder的情况下调用sequence.nextval过程中。
SV 锁(dfs lock handel) 是调用sequence.nextval期间拥有的锁。前提是创建sequence时指定了cache 和order属性 (cache+order)。order参数的目的是为了在RAC上节点之间生成sequence的顺序得到保障。

创建sequence赋予的cache值较小时,有enq:sq-contention等待增加的趋势。
cache的缺省值是20.因此创建并发访问多的sequence时,cacheh值应取大一些。否则会发生enq:sq-contention等待事件。

rac上创建sequence时,如果指定了cache大小而赋予noorder属性,则各节点将会把不同范围的sequence值cache到内存上。若两个节点之间都必须通过依次递增方式使用sequence,必须赋予如下的order属性(一般不需要这样做)"sql> create sequence seq_b cache 100 order"。如果是已赋予了cache+order属性的sequence,oracle使用SV锁进行同步。SV锁争用问题发生时的解决方法与sq锁的情况相同,就是将cache 值进行适当调整。

在RAC多节点环境下,Sequence的Cache属性对性能的影响很大。应该尽量赋予cache+noorder属性,并要给予足够的 cache值。如果需要保障顺序,必须赋予cache+order属性。但这时为了保障顺序,实例之间需要不断的交换数据。因此性能稍差。


3. Oracle Sequence的创建

Oracle创建序列是我们最常用的操作之一,下面就为您详细介绍Oracle创建序列及查询序列的语法知识,希望对您能够有所帮助。

Oracle创建序列

create sequence seq_a minvalue 1000 maxvalue 99999999 start with 1000 increment by 1 nocache; 



查询序列

  1. selectseq_a.nextvalfromdual;

为每张表生成对应的序列

--创建存储过程

  1. createorreplaceprocedurep_createseq(tablenameinvarchar2)
  2. is
  3. strsqlvarchar2(500);
  4. begin
  5. strsql:='createsequenceseq_'||tablename||'minvalue1000maxvalue99999999startwith1000incrementby1nocache';
  6. executeimmediatestrsql;
  7. endp_createseq;
  8. /

--Oracle创建序列

  1. execp_createseq('t_power');
  2. execp_createseq('t_roler');
  3. execp_createseq('t_roler_power');
  4. execp_createseq('t_department');
  5. execp_createseq('t_quarters');
  6. execp_createseq('t_quarters_roler');
  7. execp_createseq('t_emp');
  8. execp_createseq('t_require_plan');
  9. execp_createseq('t_require_minutia');
  10. execp_createseq('t_require_audit');
  11. execp_createseq('t_engage');
  12. execp_createseq('t_home');
  13. execp_createseq('t_education');
  14. execp_createseq('t_works');
  15. execp_createseq('t_skill');
  16. execp_createseq('t_account');
  17. execp_createseq('t_licence');
  18. execp_createseq('t_title');
  19. execp_createseq('t_remove');
  20. execp_createseq('t_train');
  21. execp_createseq('t_pact');
  22. execp_createseq('t_assess');
  23. execp_createseq('t_attendance');
  24. execp_createseq('t_reward_punish');
  25. execp_createseq('t_dimission');
  26. execp_createseq('t_emp_roler');
  27. execp_createseq('t_code');



分享到:
评论

相关推荐

    oracle中的sequence实现主键增长

    Oracle中的Sequence是数据库管理系统提供的一种机制,用于生成序列化的整数,通常用于主键或唯一标识符,确保数据的唯一性和有序性。在Oracle中,Sequence不同于其他数据库系统的自增字段,例如SQL Server中的`...

    Oracle数据库表序列ID自增生成器

    Oracle数据库在设计和管理大型数据系统时扮演着关键角色,其中序列(Sequence)是一种非常重要的对象,用于生成唯一的整数序列,通常用作表的主键。本篇将深入探讨Oracle数据库表序列ID自增生成器及其相关知识点。 ...

    oracle序列主键自增长

    在Oracle数据库系统中,主键是表中的一个或多个字段,用于唯一标识每条记录,确保数据的完整性和一致性。为了实现主键自动增长,Oracle提供了序列(Sequences)这一特性,它能按照预设的规则(通常是递增)生成唯一...

    Oracle中主键自增实例

    在Oracle数据库系统中,主键(Primary Key)是表中的一个或一组字段,它们的值在整张表中是唯一的,用于唯一标识每一条记录。然而,与MySQL等其他数据库不同,Oracle并没有内置的自动递增(auto-increment)功能来...

    Oracle主键自增的办法

    在 Oracle 数据库中,与许多其他关系型数据库系统不同,它并没有内置的自动自增字段类型,例如 MySQL 的 `AUTO_...需要注意的是,虽然这种方法很实用,但在高并发环境下可能需要考虑序列值的并发问题,以及性能优化。

    Oracle数据库中主键的智能生成.pdf

    在Oracle数据库中,主键是表的一个或多个字段,用于唯一标识表中的每一条记录,确保数据的完整性和一致性。由于Oracle数据库不直接支持类似Access或SQL Server中的自增字段,因此在Oracle中生成主键通常需要利用序列...

    基本数据库概念 基本数据库概念

    本文将深入探讨基本的数据库概念,包括并发处理、触发器、存储过程、事务处理、SQL语言、视图、序号生成器等核心主题。 1. **并发处理**:在多用户环境中,数据库系统需要同时处理多个用户的请求,这就是并发处理。...

    Oracle数据库基础教程:入门其实很简单

    - 数据库的概念:介绍数据库的基本定义,以及其在信息化社会中的重要作用。 - 数据库的发展历程:从早期的文件系统到关系型数据库的发展历程。 - Oracle数据库的地位:作为目前最流行的客户/服务器数据库之一,...

    oracle 存储过程使用 sequence

    Oracle存储过程是数据库管理系统中一...在设计和实现时,我们需要考虑并发控制、性能优化以及业务需求的灵活性,以确保Sequence的正确性和效率。通过熟练掌握这些技巧,我们可以编写出更加高效、可靠的数据库应用程序。

    oracle触发器实现主键自动增长

    在Oracle数据库中,实现主键自动增长是一种常见的需求,尤其在设计高并发、大数据量的系统时,确保每个记录都有一个唯一且连续的标识符变得至关重要。本文将深入探讨如何利用Oracle触发器来实现这一功能,同时也会...

    oracle 自增长主键.doc

    Oracle数据库系统中,自增长主键是一种常见的设计模式,用于创建具有自动递增的唯一标识符。在Oracle中,我们通常使用序列(Sequence)来实现这个功能,而不是像其他数据库系统那样使用内置的自增机制。以下是对...

    oracle 锁及并发性

    #### 一、Oracle 锁的概念与作用 在数据库管理系统(DBMS)中,锁是一种用于控制多个用户对共享资源访问的机制。它确保了数据的一致性和完整性,特别是在多用户环境中。Oracle数据库提供了多种类型的锁来管理并发访问...

    Oracle 11g 从入门到精通——第十章(视频教程)

    序列是Oracle 11g中的一个特殊对象,用于生成唯一的整数序列,常用于主键的自动递增。序列可以预定义起始值、步长、缓存大小等参数。使用`CREATE SEQUENCE`语句创建序列,然后在插入新行时通过`NEXTVAL`获取序列的...

    Oracle数据库培训1.ppt

    Oracle数据库是一种广泛使用的大型关系型数据库管理系统,以其强大的性能、高度的可移植性和出色的安全性而闻名。在Oracle数据库培训中,以下是一些关键知识点的详细介绍: 一、Oracle数据库简介 Oracle数据库是...

    oracle数据库应用与开发讲义

    - **序列**:通过CREATE SEQUENCE语句创建序列,通常用于生成唯一标识符。 - **视图**:使用CREATE VIEW语句创建视图,可以包含JOIN、GROUP BY等复杂操作。 - **索引**:通过CREATE INDEX语句创建索引,优化查询性能...

    oracle快速批量生成表和触发器和序列.zip

    在Oracle数据库管理中,批量生成表、触发器和序列是一项常见的任务,特别是在系统初始化或数据迁移时。...但也要注意,过度依赖触发器可能导致性能下降,因此在设计时应考虑优化策略,并确保数据库设计符合最佳实践。

    Hibernate中主键生成策略

    在Java的持久化框架...理解并正确选择主键生成策略对于优化数据库性能、保证数据的唯一性和完整性以及适应不同的数据库环境都至关重要。在实际开发中,应根据项目需求和所使用的数据库类型来选择最合适的策略。

    Oracle数据库的完整性约束和序列规则详解

    Oracle数据库的完整性约束和序列规则是数据库管理中的关键概念,它们确保了数据的准确性和一致性。完整性约束主要有五种类型:Check约束、NOT NULL约束、Unique约束、Primary键约束和Foreign key约束。 Check约束...

    Oracle数据库学习笔记 四

    本篇笔记主要探讨了Oracle数据库中的一些关键概念,包括数据定义语言(DDL)、锁机制、分区、同义词、序列、视图以及索引。 1. **数据定义语言 (DDL)**: DDL是SQL的一部分,用于创建和修改数据库对象,如表格、视图...

Global site tag (gtag.js) - Google Analytics