[20181007]Scalable sequences oracle database 12c.txt

[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更好控制一些.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2215504/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2215504/