oracle筛查断号,ORACLE易错44<SQL14>sequence中的cache

3.14 sequence中的cache

开发中使用sequence是极其常见的，使用中要注意什么呢？特别想说的就是sequence是产生一系列连续的不重复数字，但是这个序列很容易产生断号，尤其是在有cache的时候，如果应用有特别要求，一定要中间不丢连续号，那就要小心了。具体演示如下：

SQL> connect ljb/ljb

Connected to Oracle Database 10g Enterprise Edition Release

10.2.0.1.0

Connected as ljb

建立一个sequence 并设置cache为20

SQL> create sequence ljb_test_seq

2 minvalue 1

3 maxvalue 1000

4 start with 1

5 increment by 1

6 cache 20

7 order;

Sequence created

刚刚创建的序列必须先用 nextval 来获取一个初始值，否则会提示08002错误

SQL> select ljb_test_seq.currval from dual;

ORA-08002: 序列 LJB_TEST_SEQ.CURRVAL 尚未在此会话中定义

SQL> select ljb_test_seq.nextval from dual;

NEXTVAL

---------------------

1

SQL> select ljb_test_seq.currval from dual;

CURRVAL

-----------------------

1

SQL> alter system flush shared_pool;

System altered

内存清空后查看当前的序列还是1

SQL> select ljb_test_seq.currval from dual;

CURRVAL

----------

1

但是我们惊奇的发现下一个序列居然不是2，而是21，清空内存后丢了20个连续号码

SQL> select ljb_test_seq.nextval from dual;

NEXTVAL

----------

21

再确认一下，再次清空内存！

SQL> alter system flush shared_pool;

System altered

发现当前的序列为21

SQL> select ljb_test_seq.currval from dual;

CURRVAL

----------

21

不过下一个序列变成是41而不是22了，清空内存后又丢了20个

SQL> select ljb_test_seq.nextval from dual;

NEXTVAL

---------------

41

通过这个实验我们明白了，sequence的cache原来是把后面要用的序列先保存到cache

中缓存起来，以便下一次从内存中读出来，加快速度！cache

20就是缓存20个，而我这没次都用清空内存的方式，所以导致序列不断丢失！

还有什么情况会丢失连续号呢？继续实验如下，现在我们不再使用alter system flush

shared_pool了，看看怎么丢失连续号码的：

先建一测试表，让序列插入表中

SQL> drop table ljb_test;

Table dropped

SQL> create table ljb_test(id1 int);

Table created

插入如下几条记录

SQL> insert into ljb_test values

(ljb_test_seq.nextval);

1 row inserted

SQL> insert into ljb_test values

(ljb_test_seq.nextval);

1 row inserted

SQL> insert into ljb_test values

(ljb_test_seq.nextval);

1 row inserted

查询表ljb_test，发现确有记录插入，并且都是连续号码

SQL> select * from ljb_test;

ID1

------------------------------

42

43

44

SQL> commit;

Commit complete

继续再插入一条，发现还是连续的，没问题！

SQL> insert into ljb_test values

(ljb_test_seq.nextval);

1 row inserted

SQL> select * from ljb_test;

ID1

--------------------------

42

43

44

45

如果我们这个时候回滚，发现序号不再连续了！

SQL> rollback;

Rollback complete

SQL> insert into ljb_test values

(ljb_test_seq.nextval);

1 row inserted

SQL> select * from ljb_test;

ID1

---------------------------------------

42

43

44

46

此外如果插入失败，序号也不会连续！这里就不再构造例子了。

总结：通过上述实验，我们可以明白，其实sequece要保证连续中间不断号是很困难的，如果我们是采用cache的形式，重启数据库或者内存被清空等情况都会导致缓存在cache中的连续号被清空，就产生断号了，其实即便我们不设置cache，比如设置了nocache的关键字，一样会丢失，前面说过，回滚或者插入失败都会导致序列断号，这可和cache无关啊。因此这里我想说的是如果你要保证你的应用的该列是绝对要连续的，考虑sequence是很难保证的！即便nocache都不行！要考虑编写代码自己来解决问题了。

但是实际应用中，90%以上的序列都不会绝对要求是连续不中断的，此时sequence＋cache就发挥了巨大的作用了。我的观点是没有特别的不断号需求，最好都使用sequence,并且加上cache,并且尽量不要太小，当然值太大也浪费，我们测试的结果是一般不要超过300，再往上性能提升不明显了。带cache的sequence能带来两个好处：１、速度能提高，２、ＲＡＣ环境可避免争用，两个实例可各自缓存一部分sequence值。我记得在环境组上班的时候曾经和同事一起优化过sequence，就是要求开发将所有的带sequence的脚本都加上cache

，后来测试发现对表的插入速度有了显著的提高，并且也缓解了RAC环境的争用。

此外我还想说一点，既然sequence＋nochache 也不能绝对保证不断号，这个nocache也没什么用了，所以sequece指定nocache的方式可以抛弃掉了。