[20181007]Scalable sequences oracle database 12c.txt

[20181007]Scalable sequences oracle database 12c.txt

--//連結 :

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