CSDN博客

img black_snail

从不sequential的sequence

发表于2003/12/2 22:02:00  1010人阅读

       遇到过好多问题关于如何在ORACLE 中创建类似SQLSERVERACCESS自增长字段。答案多是先建立一个Sequence,然后在Trigger中将SequenceNEXTVAL的取值赋予所需要的列。看上去还不错。

       

        但是一切真的那么顺利吗?Sequence 真的可以做到提供一序列连续没有遗漏的序列数值吗?

         不妨作个实验:

 

SQL> create sequence test_seq start with 1;

Sequence created.

SQL> create table test_tab ( x int) ;

Table created.

SQL> insert into test_tab values (test_seq.nextval) ;

1 row created.

SQL> insert into test_tab values (test_seq.nextval) ;

1 row created.

SQL> insert into test_tab values (test_seq.nextval) ;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_tab ;

X
----------
1
2

3

SQL> conn / as sysdba;
Connected.
SQL> alter system flush shared_pool ;

System altered.

SQL> conn user1/user1
Connected.
SQL> insert into test_tab values (test_seq.nextval) ;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_tab ;

X
----------
1
2

3
21

 

 

         从试验中可以看出,在缺省情况下,我们建立的是带有Cache选项的Sequence (缺省值是20), 它的作用是预先将一定数量的序列值存放在SGA中,便于快速访问。可是它的副作用就是这部分数值可能会被清除, 当下一次获取NEXTVAL时,就会不可避免地造成序列值丢失。

         总结一下,在以下情况下,序列值会丢失:

 

1.  数据库关闭或重起 ,由于整个SGA会被清除,所以Cached的序列值同样会被清除。

2.        类似于普通的Data Block ,SGA中需要放置新的数据,Cached的序列值可能会按照SGA的数据存放规则被清除。

 

读到这里,细心的读者也许会问,如果在创建Sequence时,有意不选用Cache选项,问题不就解决了吗?且慢,还有两点需要注意:

 

1 访问效率降低,没有Cache功能的Sequence取值将无法直接访问内存

2 不论是Nocache还是Cache , 每次访问NEXTVAL的过程都是不可逆的,在同一session中,在执行一系列DMLSequence的操作后,用户执行Rollback,希望将操作回滚,但是Sequence此时就显得异常顽固,用掉的NEXTVAL将无法被重现。当下一次试图读取NEXTVAL时,Sequence的指针又移动到下一位了。

 

        看来Oracle真是一个海洋,每个细小的知识点都是那么饶有趣味,值得我们去努力专研啊。

 

 

备注:使用Cache功能对Sequence读取效率的影响

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
 
SQL> set timing on
SQL> set autotrace traceonly statistics
SQL> SELECT * FROM ALL_OBJECTS;
14302 rows selected.
Elapsed: 00:00:13.05
Statistics
----------------------------------------------------------
          7  recursive calls
          4  db block gets
     146635  consistent gets
          0  physical reads
          0  redo size
    1633344  bytes sent via SQL*Net to client
     117520  bytes received via SQL*Net from client
        956  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14302  rows processed
 
SQL> 
SQL> -- 测试带有CACHE选项的Sequence:
SQL> 
SQL> CREATE SEQUENCE test_seq1 CACHE 1000;
 
Sequence created.
 
Elapsed: 00:00:00.00
SQL> SELECT x.*, test_seq1.NEXTVAL FROM ALL_OBJECTS x;
 
14303 rows selected.
 
Elapsed: 00:00:13.09
 
Statistics
----------------------------------------------------------
        202  recursive calls
         64  db block gets
     146636  consistent gets
          0  physical reads
      10468  redo size
    1752002  bytes sent via SQL*Net to client
     117543  bytes received via SQL*Net from client
        956  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14303  rows processed
 
SQL> 
SQL> --测试不带有CACHE选项的Sequence
SQL> 
SQL> DROP SEQUENCE test_seq1;
 
Sequence dropped.
 
Elapsed: 00:00:00.00
SQL> CREATE SEQUENCE test_seq1 NOCACHE;
 
Sequence created.
 
Elapsed: 00:00:00.00
SQL> SELECT x.*, test_seq1.NEXTVAL FROM ALL_OBJECTS x;
 
14303 rows selected.
 
Elapsed: 00:00:32.02        (执行时间明显长了)
 
Statistics
----------------------------------------------------------
     185946  recursive calls
      57216  db block gets
     160925  consistent gets
          0  physical reads
   10004008  redo size
    1752002  bytes sent via SQL*Net to client
     117543  bytes received via SQL*Net from client
        956  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      14303  rows processed

 

       

                                                                BLACK_SNAIL

                                                                欢迎交流,转载注明

                                                             ligang1000@hotmail.com/ligang@fujitsu.sh.cn                                                                                                                      

0 0

相关博文

我的热门文章

img
取 消
img