在12C之前，需要对一个表创建序列自动增长的话，首先需要创建索引，然后在插入数据的时候再使用序列，但在12C中，可以直接在创建表时就指定序列，当有数据插入的时候，序列自动增长填充此列，当使用implicit sequence的时候，默认会cache 20个值。
1. 创建索引，创建隐式序列的表
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> create sequence jerry_seq; Sequence created. SQL> CREATE TABLE jerry_test 2 ( 3 transaction_id NUMBER DEFAULT jerry_seq.NEXTVAL, 4 transaction_type NUMBER, 5 transaction_date DATE 6 ); Table created. |
2. 往表中插入值的时候，transaction_id会自动增长
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SQL> insert into jerry_test(transaction_type,transaction_date) values(111,sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from jerry_test; TRANSACTION_ID TRANSACTION_TYPE TRANSACTI -------------- ---------------- --------- 1 111 24-JUN-14 SQL> insert into jerry_test(transaction_type,transaction_date) values(111,sysdate); 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> / 1 row created. SQL> select * from jerry_test; TRANSACTION_ID TRANSACTION_TYPE TRANSACTI -------------- ---------------- --------- 1 111 24-JUN-14 2 111 24-JUN-14 3 111 24-JUN-14 4 111 24-JUN-14 5 111 24-JUN-14 |
3. 测试手动往这种隐式序列添加值，对序列是毫无影响的
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
------------此时表中还没有transaction_id=7 SQL> insert into jerry_test(transaction_id,transaction_type,transaction_date) values(7,111,sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from jerry_test; TRANSACTION_ID TRANSACTION_TYPE TRANSACTI -------------- ---------------- --------- 1 111 24-JUN-14 2 111 24-JUN-14 3 111 24-JUN-14 4 111 24-JUN-14 5 111 24-JUN-14 7 111 24-JUN-14 6 rows selected. ------------继续序列填充值 SQL> insert into jerry_test(transaction_type,transaction_date) values(111,sysdate); 1 row created. SQL> select * from jerry_test; TRANSACTION_ID TRANSACTION_TYPE TRANSACTI -------------- ---------------- --------- 1 111 24-JUN-14 2 111 24-JUN-14 3 111 24-JUN-14 4 111 24-JUN-14 5 111 24-JUN-14 7 111 24-JUN-14 6 111 24-JUN-14 7 rows selected. ------------序列还是会创建一模一样的transaction_id=7 SQL> insert into jerry_test(transaction_type,transaction_date) values(111,sysdate); 1 row created. SQL> select * from jerry_test; TRANSACTION_ID TRANSACTION_TYPE TRANSACTI -------------- ---------------- --------- 1 111 24-JUN-14 2 111 24-JUN-14 3 111 24-JUN-14 4 111 24-JUN-14 5 111 24-JUN-14 7 111 24-JUN-14 6 111 24-JUN-14 7 111 24-JUN-14 8 rows selected. |
当然使用序列好像都存在这样一个gaps，因为这种隐式序列默认是要cache 20个值是SGA中的，如果flush的SGA的话，那共享池就没有cache的记录了，导致序列不连续。
当然也可以使用alter命令修改一个表的列使用隐式序列方式填充
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> ALTER TABLE jerry_test MODIFY transaction_type DEFAULT jerry_seq.NEXTVAL; Table altered. SQL> insert into jerry_test(transaction_date) values(sysdate); 1 row created. SQL> select * from jerry_test; TRANSACTION_ID TRANSACTION_TYPE TRANSACTI -------------- ---------------- --------- 1 111 24-JUN-14 2 111 24-JUN-14 3 111 24-JUN-14 4 111 24-JUN-14 5 111 24-JUN-14 7 111 24-JUN-14 25 111 24-JUN-14 26 111 24-JUN-14 27 111 24-JUN-14 28 28 24-JUN-14 10 rows selected. |
- 本文固定链接: http://www.savedba.com/?p=729
- 转载请注明: 版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!
