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

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

         不妨作個實驗：

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), 它的作用是預先將一定數(shù)量的序列值存放在SGA中，便于快速訪問。可是它的副作用就是這部分數(shù)值可能會被清除, 當下一次獲取NEXTVAL時，就會不可避免地造成序列值丟失。

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

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

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

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

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

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

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

備注：使用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        （執(zhí)行時間明顯長了）

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