[20181007]Scalable sequences oracle database 12c.txt --//链接 : https://oracle-base.com/articles/18c/scalable-sequences-18c Scalable Sequences in Oracle Database 18c Scalable sequences are designed to reduce problems with hot index blocks during large scale data loads into tables with sequence generated primary keys on single instance and RAC databases. Scalable sequences have been available since the first release of Oracle 12c, but they were not documented and therefore not supported. They are included in the documentation for the first time in Oracle 18c, so they are now a supported feature.     The Problem     Creating Scalable Sequences     Altering Scalable Sequences     Views     Implications Related articles.     Oracle Sequences The Problem A sequence is a database object used to produce unique integers, which are commonly used to populate a synthetic primary key column in a table. Since the sequence number always increases, typically by 1, each new entry is placed on the right-most leaf block of the index, making it a hot block. By itself this can cause contention, but things can get worse when dealing with a RAC database, where the contention on the right-most leaf block can cause cluster waits, with the RAC instances fighting over the block. Since Oracle 8 you might have used reverse key indexes to solve this problem, but scalable sequences may be a better solution. Creating Scalable Sequences A scalable sequence adds a 6 digit prefix to the sequence. The prefix is made up of a 3 digit instance offset concatenated to a 3 digit session offset, which the documentation describes as follows. [(instance id % 100) + 100] || [session id % 1000] The final sequence number is in the format "prefix || zero-padding || sequence", where the amount of padding depends on the definition of the sequence. With the introduction of scalable sequences, the default attribute for a sequence is NOSCALE, but you can specify it explicitly if you wish. You will see it functions like a normal sequence. --//实际上12cR2版本已经支持这个特性,自己简单测试看看: 1.环境: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试: SCOTT@test01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------          9      51442 2352:2256                DEDICATED 2952                      24          3 alter system kill session '9,51442' immediate; --//我的测试环境仅仅1个实例. SCOTT@test01p> CREATE SEQUENCE scale_seq START WITH 1 MAXVALUE 9999999 SCALE; Sequence created. --//按照前面的格式介绍 "prefix || zero-padding || sequence",前面的inst_id,sid已经占了6位(十进制).这样实际上仅仅用户1位. SCOTT@test01p> SELECT scale_seq.nextval FROM dual;    NEXTVAL ----------    1010091 --//再打开另外1个会话: SCOTT@test01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------          6      33953 3316:1380                DEDICATED 3656                      28         41 alter system kill session '6,33953' immediate; SCOTT@test01p> SELECT scale_seq.nextval FROM dual;    NEXTVAL ----------    1010062 --//如果连续取sequence. .... SCOTT@test01p> SELECT scale_seq.nextval FROM dual;    NEXTVAL ----------    1010069 SCOTT@test01p> SELECT scale_seq.nextval FROM dual; SELECT scale_seq.nextval FROM dual * ERROR at line 1: ORA-64603: NEXTVAL cannot be instantiated for SCALE_SEQ. Widen the sequence by 1 digits or alter sequence with SCALE EXTEND. --//留给seq位数仅仅1位,也就是到9就满了. 3.另外可以定义sequence EXTEND(缺省是NOEXTEND). SCOTT@test01p> CREATE SEQUENCE scale_ext_seq START WITH 9 MAXVALUE 999 SCALE EXTEND; Sequence created. SCOTT@test01p> @spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------          6      33953 3316:1380                DEDICATED 3656                      28         41 alter system kill session '6,33953' immediate; SCOTT@test01p> SELECT scale_ext_seq.nextval FROM dual;    NEXTVAL ----------  101006009 SCOTT@test01p> SELECT scale_ext_seq.nextval FROM dual;    NEXTVAL ----------  101006010 --//这样实际上前面有6位留给了inst_id,sid. 后面才是sequence.这样最大的好处避免索引插入在相同的数据块,特别在rac环境有用. 4.另外以前的seq也可以修改属性加入scale,EXTEND. SCOTT@test01p> CREATE SEQUENCE seq1 START WITH 1 MAXVALUE 99999999999 ; Sequence created. SCOTT@test01p> SELECT sequence_name, min_value, max_value, scale_flag, extend_flag FROM user_sequences where sequence_name='SEQ1' ; SEQUENCE_NAME              MIN_VALUE       MAX_VALUE S E -------------------- --------------- --------------- - - SEQ1                               1     99999999999 N N SCOTT@test01p> SELECT seq1.nextval FROM dual;         NEXTVAL ---------------               1 SCOTT@test01p> ALTER SEQUENCE seq1 SCALE NOEXTEND; Sequence altered. SCOTT@test01p> @ spid             SID         SERIAL# PROCESS                  SERVER    SPID                     PID       P_SERIAL# C50 --------------- --------------- ------------------------ --------- -------------------- ------- --------------- --------------------------------------------------               9           51442 2352:2256                DEDICATED 2952                      24               3 alter system kill session '9,51442' immediate; SCOTT@test01p> SELECT seq1.nextval FROM dual;         NEXTVAL ---------------     10100900002 SCOTT@test01p> ALTER SEQUENCE seq1 SCALE EXTEND; Sequence altered. SCOTT@test01p> SELECT seq1.nextval FROM dual;         NEXTVAL --------------- 1.010090000E+16     SCOTT@test01p> set numw 17 SCOTT@test01p> SELECT seq1.nextval FROM dual;           NEXTVAL ----------------- 10100900000000005 SCOTT@test01p> ALTER SEQUENCE seq1 NOSCALE; Sequence altered. SCOTT@test01p> SELECT seq1.nextval FROM dual; SELECT seq1.nextval FROM dual        * ERROR at line 1: ORA-08004: sequence SEQ1.NEXTVAL exceeds MAXVALUE and cannot be instantiated --//因为这个时候最大值是10100900000000005,已经超出了定义. SCOTT@test01p> SELECT sequence_name, min_value, max_value, scale_flag, extend_flag FROM user_sequences where sequence_name='SEQ1' ; SEQUENCE_NAME                MIN_VALUE         MAX_VALUE S E -------------------- ----------------- ----------------- - - SEQ1                                 1       99999999999 N N SCOTT@test01p> SELECT seq1.currval FROM dual;           CURRVAL ----------------- 10100900000000005 --//我个人的感觉加入EXTEND更好控制一些.