I have encountered a lot of questions about how to create a self-growth field similar to SQLServer or Access in Oracle. The answer is mostly to build a Sequence, then give the required columns in the Trigger's NEXTVAL. It looks good.
But is it true that everything is so smooth? Sequence can really be provided with a sequence value of the sequence without missing sequences?
Should be experimentation:
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. Next Created.sql> Insert INTO TEST_TAB VALUES (TEST_SEQ.NEXTVAL); 1 row created.sql> commit; commit complete.sql> selection * from test_tab; x -------- 12
3SQL> conn / as sysdba; Connected.SQL> alter system flush shared_pool; System altered.SQL> conn user1 / user1Connected.SQL> insert into test_tab values (test_seq.nextval); 1 row created.SQL> commit; Commit complete.SQL > select * from test_tab; x ---------- 12
321
As can be seen from the experiment, in the default, we build Sequence (default 20) with the cache option, which is to store a certain number of sequence values in SGA, easy to access . But its side effect is that this part of the value may be cleared. When you get NextVal, you will inevitably cause loss of sequence values.
Summary, in the following cases, the sequence value will be lost:
1. The database is turned off or restarted, and since the entire SGA will be cleared, the sequence value of the Cached will also be cleared.
2. Similar to ordinary Data Block, when new data is placed in SGA, the sequence value of the cached may be cleared in accordance with SGA's data storage rules.
Read here, careful readers may ask, if you intend to use the cache option when you create sequence, is there any problem? And slow, there are two points to pay attention:
1. If the access efficiency is lowered, the sequence values without cache feature will not be able to access memory directly.
2. Whether it is NOCACHE or cache, each access nextVal is irreversible, in the same session, after executing a series of DML and SEQUENCE operation, the user performs Rollback, I hope to roll back, but sequence is very abnormal. Stubborn, useful NextVal will not be reproduced. When the next time I tried to read NextVal, the SEQUENCE's pointer moved to the next one.
It seems that Oracle is really a ocean, and every small knowledge point is so fun, worthy of our efforts.
Note: The impact of using the Cache function on the read efficiency of the sequence
Connected to:
Oracle8i
Enterprise
Edition Release 8.1.7.4.1 - production
WITH THE PartInog 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> - Test Sequence with Cache options:
SQL>
SQL> CREATE SEQUENCE TEST_SEQ1 CACHE 1000;
Sequence created.
Elapsed: 00: 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 Get
146636 CONSISTENT GETS
0 Physical READS
10468 Redo Size
1752002 BYTES SENT VIA SQL * NET to Client
117543 BYtes Received Via SQL * NET from Clom Clom Clom
956 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
14303 ROWS Processed
SQL>
SQL> - SEQUENCE without cache options
SQL>
SQL> DROP SEQUENCE TEST_SEQ1;
Sequence Dropped.
Elapsed: 00: 00: 00:00.00
SQL> CREATE SEQUENCE TEST_SEQ1 NOCACHE;
Sequence created.
Elapsed: 00: 00: 00:00.00
SQL> SELECT X. *, Test_SEQ1.NEXTVAL from ALL_OBJECTS X;
14303 Rows SELECTED.
ELAPSED: 00: 00: 32.02 (Execution time is significantly longer)
Statistics
-------------------------------------------------- ------------
185946 Recursive Calls
57216 DB Block Get
160925 CONSISTENT GETS
0 Physical READS
10004008 Redo Size1752002 BYTES SENT VIA SQL * NET to Client
117543 BYtes Received Via SQL * NET from Clom Clom Clom
956 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
14303 ROWS Processed
Black_snail
Welcome to exchange, reprint
Ligang1000 @ hotmail.com / ligang @ fujitsu.sh.cn
