遇到過(guò)好多問(wèn)題關(guān)于如何在ORACLE 中創(chuàng)建類似SQLSERVER或ACCESS中自增長(zhǎng)字段。答案多是先建立一個(gè)Sequence，然后在Trigger中將Sequence的NEXTVAL的取值賦予所需要的列。看上去還不錯(cuò)。

        但是一切真的那么順利嗎？Sequence 真的可以做到提供一序列連續(xù)沒(méi)有遺漏的序列數(shù)值嗎？

         不妨作個(gè)實(shí)驗(yàn)：

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

         從試驗(yàn)中可以看出，在缺省情況下，我們建立的是帶有Cache選項(xiàng)的Sequence (缺省值是20), 它的作用是預(yù)先將一定數(shù)量的序列值存放在SGA中，便于快速訪問(wèn)?？墒撬母弊饔镁褪沁@部分?jǐn)?shù)值可能會(huì)被清除, 當(dāng)下一次獲取NEXTVAL時(shí)，就會(huì)不可避免地造成序列值丟失。

         總結(jié)一下，在以下情況下，序列值會(huì)丟失：

1．  數(shù)據(jù)庫(kù)關(guān)閉或重起 ，由于整個(gè)SGA會(huì)被清除，所以Cached的序列值同樣會(huì)被清除。

2.        類似于普通的Data Block ,當(dāng)SGA中需要放置新的數(shù)據(jù)，Cached的序列值可能會(huì)按照SGA的數(shù)據(jù)存放規(guī)則被清除。

讀到這里，細(xì)心的讀者也許會(huì)問(wèn)，如果在創(chuàng)建Sequence時(shí)，有意不選用Cache選項(xiàng)，問(wèn)題不就解決了嗎？且慢，還有兩點(diǎn)需要注意：

1． 訪問(wèn)效率降低，沒(méi)有Cache功能的Sequence取值將無(wú)法直接訪問(wèn)內(nèi)存

2． 不論是Nocache還是Cache , 每次訪問(wèn)NEXTVAL的過(guò)程都是不可逆的，在同一session中，在執(zhí)行一系列DML和Sequence的操作后，用戶執(zhí)行Rollback,希望將操作回滾，但是Sequence此時(shí)就顯得異常頑固，用掉的NEXTVAL將無(wú)法被重現(xiàn)。當(dāng)下一次試圖讀取NEXTVAL時(shí)，Sequence的指針又移動(dòng)到下一位了。

        看來(lái)Oracle真是一個(gè)海洋，每個(gè)細(xì)小的知識(shí)點(diǎn)都是那么饒有趣味，值得我們?nèi)ヅＱ邪　?/SPAN>

備注：使用Cache功能對(duì)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> -- 測(cè)試帶有CACHE選項(xiàng)的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> --測(cè)試不帶有CACHE選項(xiàng)的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        （執(zhí)行時(shí)間明顯長(zhǎng)了）

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

                                                                歡迎交流，轉(zhuǎn)載注明

                                                             ligang1000@/ligang@fujitsu.sh.cn