18c新特性：Scalable Sequences(自适应序列)

作为18c的新特性,其实在oracle 12.2 引入了Scalable Sequences作为一个隐藏特性，为了改善在高并发系统中，特别是使用seq作为index，大量插入记录导致index的争用，oracle自己实现了以前我们需要人工去自定义seq的方式(instance+sid+seq的类似算法方式）,该功能将在oracle 18c中正式推出,我这里带领大家先体会下
SCALE/NOSCALE
When SCALE is specified, a numeric offset is affixed to the beginning of the sequence. This offset if of the form iii||sss||, where,
iii denotes a three digit instance offset given by (instance_id % 100) + 100,
sss denotes a three digits session offset given by (session_id % 1000), and
|| is the concatenation operator

EXTEND/NOEXTEND
When EXTEND is specified with the SCALE keyword, the generated sequence values are all of length (x+y), where x is the length of the scalable offset (default 6), and y is the maximum number of digits in the sequence maxvalue/minvalue. Thus, for an ascending sequence with maxvalue 100 and SCALABLE EXTEND specified, the generated sequence values are of the form iii||sss||001, iii||sss||002, …,iii||sss||100
The default setting for the SCALE clause is NOEXTEND. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence maxvalue/minvalue. This setting is useful for integration with existing applications where sequences are used to populate fixed width columns. On invocation of NEXTVAL on a sequence with SCALABLE NOEXTEND specified, a user error is thrown if the generated value requires more digits of representation than the sequence’s maxvalue/minvalue.

SCALE的算法就是(instance_id % 100)(_kqdsn_instance_digits) + 100||(session_id % 1000)(_kqdsn_cpu_digits)+seq(EXTEND/NOEXTEND确定是否固定宽度）

Scalable Sequences语法

CREATE | ALTER SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE}
]

Scalable Sequences测试

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 23 02:33:49 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0


SQL> set line 150 
SQL> col "Paramete"  for a30 
SQL> col "Session Value"  for a20
SQL> col "Instance Value" for a20
SQL> col "Is Default?" for a20
SQL> SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value",c.ksppstdf "Default?" 
  2  FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx  AND a.indx = c.indx  AND lower(a.ksppinm) 
  3  in ('_kqdsn_instance_digits', '_kqdsn_cpu_digits');

Parameter                                                Session Value        Instance Value       Default?
-------------------------------------------------------- -------------------- -------------------- ---------
_kqdsn_instance_digits                                   2                    2                    TRUE
_kqdsn_cpu_digits                                        3                    3                    TRUE

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
              1

SQL> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------------------------------
275


SQL> create sequence seq_xff start with 1 increment by 1 minvalue 1 maxvalue 100 scale extend;

Sequence created.

SQL> select seq_xff.nextval from dual;

   NEXTVAL
----------
 101275001

验证效果

--另外一个会话
[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 23 02:45:14 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
---------------------------------------------------------
4

SQL> select instance_number from v$instance;

INSTANCE_NUMBER
---------------
              1

SQL> select seq_xff.nextval from dual;

   NEXTVAL
----------
 101004002

这里可以看出来seq的值是固定长度的.而且随着sid或者inst_id 不同而不同,从而实现减少大量数据集中在一个block而引起的各种争用
测试scale noextend

SQL> create sequence seq_xifenfei start with 1 increment by 1 minvalue 1 maxvalue 100 scale noextend;

Sequence created.

SQL> select seq_xifenfei.nextval from dual;
select seq_xifenfei.nextval from dual
       *
ERROR at line 1:
ORA-64603: NEXTVAL cannot be instantiated for seq_xifenfei. Widen the sequence
by 4 digits or alter sequence with SCALE EXTEND.


SQL> drop sequence seq_xifenfei;

Sequence dropped.

SQL> create sequence seq_xifenfei start with 1 increment by 1 minvalue 1 maxvalue 1000000 scale noextend;

Sequence created.

SQL>  select seq_xifenfei.nextval from dual;

   NEXTVAL
----------
   1010041

这里可以看出来scale noextend的长度是随着seq值的改变而改变,而且max值不能小于seq本身长度.