不同于Oracle：SEQUENCE的区别

前言

在使用Oracle数据库SEQUENCE功能时，发现Oracle对边界处理比较奇怪。刚好GreatSQL也支持SEQUENCE，就拿来一起比较一下。

先说结论：GreatSQL 的使用基本和Oracle基本一致，但是对 START WITH 的边界限制有所不同。

本次测试使用数据库的版本号

# Oracle版本
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

# GreatSQL版本
greatsql> \S
...
Server version:        8.0.32-25 GreatSQL, Release 25, Revision 79f57097e3f
...
1 row in set (0.00 sec)

SEQUENCE 使用介绍

SEQUENCE 有以下几个常用的参数

INCREMENT BY 怎么用

INCREMENT BY 的值大于0时，为递增序列

INCREMENT BY 的值小于0时，为递减序列

何时能使用NOMINVALUE &NOMINVALUE

1. INCREMENT BY的值大于0时（递增序列），可以用NOMAXVALUE；
2. INCREMENT BY的值小于0时（递减序列），可以用NOMINVALUE。

To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.

CYCLE/NOCYCLE

如果是CYCLE，当序列的值超出设定的范围时，会从最大值/最小值开始重新进行循环。

递增数列从最小值开始循环，递减数列从最大值开始循环。

oracle> CREATE SEQUENCE seq1
START WITH 101
minvalue 100
INCREMENT BY -10
MAXVALUE 130
nocache
 CYCLE;

#多次执行
oracle> select seq1.nextval from dual;
#返回值依次为：
101->130->120->110>100

Oracle SEQUENCE 特性

START WITH 边界

默认情况下是认为 MINVALUE <= START WITH <= MAXVALUE，超出区间就不能创建SEQUENCE

START WITH比MINVALUE小创建失败：

oracle> create SEQUENCE MY_FIRST_SEQUENCE
start with -2
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;  2    3    4    5    6    7  
create SEQUENCE MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04006: START WITH ???? MINVALUE

START WITH比MAXVALUE大：

oracle> create SEQUENCE MY_SECOND_SEQUENCE
start with 101
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;   2    3    4    5    6    7  
create SEQUENCE MY_SECOND_SEQUENCE
*
ERROR at line 1:
ORA-04008: START WITH ???? MAXVALUE

特殊情况

在使用SEQUENCE的时候发现有两种特殊情况：

一 、当INCREMENT BY < 0 处于递减数列时

递减数列，START WITH 比 MINVALUE小1 的时候，SEQUENCE 还能正常创建：

oracle> create SEQUENCE MY_FIRST_SEQUENCE
start with -2
increment by -1
minvalue -1
maxvalue 100
nocycle
nocache;
  2    3    4    5    6    7  
Sequence created.

但是SEQUENCE 是 NOCYCLE，创建后不能使用：

oracle> select MY_FIRST_SEQUENCE.nextval from dual;
 select MY_FIRST_SEQUENCE.nextval from dual
        *
ERROR at line 1:
ORA-08004: ?? MY_FIRST_SEQUENCE.NEXTVAL goes below MINVALUE ?????

START WITH 比MINVALUE小太多就不能创建了：

oracle> create SEQUENCE MY_FIRST_SEQUENCE
start with -3
increment by -1
minvalue -1
maxvalue 100
nocycle
nocache;   2    3    4    5    6    7  
create sequence MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04006: START WITH ???? MINVALUE

oracle> drop SEQUENCE MY_FIRST_SEQUENCE;

Sequence dropped.

oracle> create SEQUENCE MY_FIRST_SEQUENCE
start with 101
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;  2    3    4    5    6    7  
create sequence MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04008: START WITH ???? MAXVALUE

oracle> create sequence MY_FIRST_SEQUENCE
start with -1
increment by -1
minvalue 1
maxvalue 100
nocycle
nocache;  2    3    4    5    6    7  
create sequence MY_FIRST_SEQUENCE
*
ERROR at line 1:
ORA-04006: START WITH ???? MINVALUE

二、当INCREMENT BY > 0 处于递增数列时

递增数列时情况相反

START WITH比MAXVALUE大1就能创建

oracle> create sequence MY_FIRST_SEQUENCE
start with 101
increment by 1
minvalue 1
maxvalue 100
nocycle
nocache;  2    3    4    5    6    7  

Sequence created.

但是 SEQUENCE 为 NOCYCLE，创建后不能使用：

oracle> select MY_FIRST_SEQUENCE.nextval from dual;
select MY_FIRST_SEQUENCE.nextval from dual
       *
ERROR at line 1:
ORA-08004: ?? MY_FIRST_SEQUENCE.NEXTVAL exceeds MAXVALUE ?????

sequence Specify the name of the sequence to be created. The name must satisfy the requirements listed in "Database Object Naming Rules". If you specify none of the clauses INCREMENT BY through GLOBAL, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with ‐1 and decreases with no lower limit. To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE. To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error. To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE.

GreatSQL 特性

GreatSQL 的使用就比较严格了： MINVALUE <= START WITH <= MAXVALUE

没发现像Oracle那样的特殊情况

greatsql> create sequence MY_FIRST_SEQUENCE
    -> start with -1
    -> increment by 1
    -> minvalue 1
    -> maxvalue 100
    -> nocycle
    -> nocache;
ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
greatsql> create sequence MY_FIRST_SEQUENCE
    -> start with 101
    -> increment by 1
    -> minvalue 1
    -> maxvalue 100
    -> nocycle
    -> nocache;
ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
greatsql> create sequence MY_FIRST_SEQUENCE
    -> start with 102
    -> increment by 1
    -> minvalue 1
    -> maxvalue 100
    -> nocycle
    -> nocache;
ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
greatsql> create sequence MY_FIRST_SEQUENCE
    -> start with 101
    -> increment by -1
    -> minvalue 1
    -> maxvalue 100
    -> nocycle
    -> nocache;
ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
greatsql> create sequence MY_FIRST_SEQUENCE
    -> start with -1
    -> increment by -1
    -> minvalue 1
    -> maxvalue 100
    -> nocycle
    -> nocache;
ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
greatsql> create sequence MY_FIRST_SEQUENCE
    -> start with 0
    -> increment by -1
    -> minvalue 1
    -> maxvalue 100
    -> nocycle
    -> nocache;
ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!
greatsql> drop sequence MY_FIRST_SEQUENCE;
ERROR 1046 (3D000): No database selected
greatsql> create sequence MY_FIRST_SEQUENCE
    -> start with -10
    -> increment by -1
    -> minvalue -9
    -> maxvalue 100
    -> nocycle
    -> nocache;
ERROR 8582 (HY000): create sequence failed, cause START WITH should between MINVALUE and MAXVALUE!  

总结

GreatSQL 和 Oracle 对 START WITH 的边界定义基本一致，都是 MINVALUE <= START WITH <= MAXVALUE，但是 Oracle 会有两个特殊情况。

相关文档

• SEQUENCE Oracle文档： https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-SEQUENCE.html#GUID-E9C78A8C-615A-4757-B2A8-5E6EFB130571 https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/Sequence-Pseudocolumns.html
• GreatSQL SEQUENCE文档： https://greatsql.cn/docs/8032-25/user-manual/5-enhance/sql-compat/5-3-easyuse-ora-syntax-sequence.html
• ORA-04013,CACHE 值必须小于CYCLE值；解决方案 https://www.cnblogs.com/PingPo/p/14312384.html