Oracle：序列 SEQUENCE 创建语法与使用

序列是oracle数据库对象一种。多个用户可以通过序列生成连续的数字以此来实现主键字段的自动、唯一增长，并且一个序列可为多列、多表同时使用。序列消除了串行化并且提高了应用程序一致性。

 

创建序列：需要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限

CREATE SEQUENCE 语法：

CREATE SEQUENCE [ schema. ]sequence

[ { INCREMENT BY | START WITH } integer

{ MAXVALUE integer | NOMAXVALUE }

{ MINVALUE integer | NOMINVALUE }

{ CYCLE | NOCYCLE }

{ CACHE integer | NOCACHE }

{ ORDER | NOORDER }

];

 

CREATE SEQUENCE参数：

Schema：指定在哪个用户的schema下创建sequence，默认当前用户。

Sequence：指定要创建的sequence序列名

INCREMENT BY：指定序列增长步长。可以为正（升序）、负整数（降序），但不能为0。最高精度28。Default间隔缺省为1。

绝对值必须小于MAXVALUE与MINVALUE的差异（如非在此区间报错：ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE）。

START WITH：指定序列起始数。默认为序列最小值1开始。

MaxValue：指定序列的最大值。整数值最多28位。必须大于等于起始值且大于等于序列最小值。

NOMAXVALUE：无最大值（实际升序为10^27或降序-1）。default

MINVALUE：指定序列的最小值。整数值最多28位。必须小于等于起始值且小于等于序列最大值。

如此处不符，报：ORA-04006: START WITH cannot be less than MINVALUE。不指定此参数时，默认是1.

NOMINVALUE：无最小值（实际为升序1或降序-10^26）。Default

CYCLE：指定序列达到最大值或最小值后继续从头开始生成。

NOCYCLE：不循环生成。Default

CACHE：指定数据库内存中预分配的序列值个数，以便快速获取。最多28位、最小cache值为2。最大值计算公式是：(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT);

不在范围会报错：ORA-04013: number to CACHE must be less than one cycle）。

如果系统发生故障，所有缓存的没有被DML语句使用并提交的序列值将丢失。潜在丢失值数量等于cache的数量，将会导致序列不连续。ORACLE建议在RAC中使用CACHE来提高性能。

NOCACHE：指定该序列值不被预分配。如果省略CACHE和NOCACHE，数据库默认会缓存20个序列号。

ORDER：只有在RAC环境时需要指定，指定order条件保证序列按请求顺序生成。在使用序列号做为一个时间戳时很有用。

NOORDER：不保证序列按请求顺序生成，这是默认的。

注意：如果只指定默认无参数序列为create sequence schema.sequence_name将启动一个从1开始，以1为单位递增，没有最大值限制的递增序列。

如果要创建一个没有约束的序列，递增序列时：忽略MAXVALUE参数或指定NOMAXVALUE；递减序列：省略MINVALUE参数或指定NOMINVALUE。

如果要创建一个有限制的序列，递增序列时：指定MAXVALUE参数；递减序列：指定MINVALUE参数。此时序列达到限制后会报错：

如果要创建一个有限制的序列在达到限制后重新启动，指定MAXVALUE和MINVALUE后，还需要指定CYCLE。如果不指定MINVALUE，默认为NOMINVALUE，这个值是1.

使用序列

序列生成的是一系列整数数字.一个序列中包含两个"伪列" ，分别为"Currval"和"Nextval",可以分别用来获取该序列的当前值和下一个值.

虽然我们在定义时指定序列初始值为1但并没有真正初始化该值. 当在检索序列的当前值前,必须通过检索序列的下一个值即Nextval来对序列进行

初始化操作.在选择了Nextval时,该序列就被初始化为1.

使用sequence时对系统性能大致有以下影响：

1.Seq$基表是记录系统sequence的数据字典表.每次调用nextval，会递归调用更新并COMMIT Seq$基表。

2.更新Seq$基表并提交会产生redo log--几百字节，COMMIT频繁会造成LGWR的压力；过多redo log生成，造成LGWR压力、恢复时费时等。

3.多个会话使用sequence可能出现争用，等待事件row lock contention

对于nocache/cache参数：

nocache：每次使用nextval，都会更新Seq$基表并COMMIT。

cache：只有在内存中cache的序列号使用完后才会重新获取sequence，才会更新Seq$基表并提交。

比如cache设置为2000，则在使用sequence时对性能影响比nocache小上千倍。

所以一般情况下，建议设置一个较大的cache值，用于进行性能的优化。（默认不指定nocache时是20）

 

ORACLE sequence修改：

ALTER SEQUENCE [ schema. ]sequence

{ INCREMENT BY integer

{ MAXVALUE integer | NOMAXVALUE }

{ MINVALUE integer | NOMINVALUE }

{ CYCLE | NOCYCLE }

{ CACHE integer | NOCACHE }

{ ORDER | NOORDER }

}

修改时的三个注意事项：

如果要使序列start with不同的数字，只能删除序列重建。

如果修改的maxvalue必须大于序列当前值。

如果在使用NEXTVAL初始化序列前改变INCREMENT BY的值，一些序列号会被跳过。解决跳过问题的方法--删除重建

修改的各个参数的新值依然要满足create sequence各参数介绍中的描述。

 

ORACLE sequence删除：

DROP SEQUENCE [ schema. ]sequence_name ;