Scalable Sequences 学习测试

一、需求

对于使用序列作为主键列，当insert过于频繁时,经常出现索引块分裂的问题？

那么Oracle在新的版本存在咋样的改进！ 12c新特性 Scalable Sequences 

 

参考

https://cloud.tencent.com/developer/news/113092

https://docs.oracle.com/en/database/oracle/oracle-database/18/admin/managing-views-sequences-and-synonyms.html#GUID-76663C84-D792-46A3-A25A-03C49DED71AD

 

二、Scalable Sequences 

2.1 官方文档 Scalable sequences 说明

General
Scalable Sequences
Memoptimized Rowstore
Scalable Sequences
A sequence can be made scalable by specifying the SCALE clause in the CREATE SEQUENCE or ALTER SEQUENCE statement. A scalable
 sequence is particularly efficient when used to generate unordered primary or unique keys for data ingestion workloads having
 high level of concurrency. Scalable sequences significantly reduce the sequence and index block contention and provide better
 data load scalability compared to the solution of configuring a very large sequence cache using the CACHE clause of CREATE SEQUENCE
 or ALTER SEQUENCE statement.

Scalable sequences improve the performance of concurrent data load operations, especially when the sequence values
 are used for populating primary key columns of tables in single Oracle database instances as well as Oracle RAC databases.

where,
|| is the concatenation operator.
6 digit scalable sequence offset number = 3 digit instance offset number || 3 digit session offset number.
The 3 digit instance offset number is generated as [(instance id % 100) + 100]. The 3 digit session offset number is
 generated as [session id % 1000].
Additionally, you can also specify EXTEND or NOEXTEND option for the SCALE clause:

EXTEND option
When the EXTEND option is specified for the SCALE clause, the scalable sequence values are of the length [X digits + Y digits],
 where X is the number of digits in the scalable sequence offset number (default is 6 digits), and Y is the number of digits
 specified in the MAXVALUE clause.
For example, for an ascending scalable sequence with MINVALUE of 1, MAXVALUE of 100 (3 digits), and EXTEND option specified,
the scalable sequence values will be of 9 digits (6 digit scalable sequence offset number + 3 digit MAXVALUE) and will be of
 the form:

可以理解为||  前6位主要目的为了对实例及会话申请的sequence进行分割,后面就是和普通序列一样的数值；
 通过实例名称，会话SID 换算后申请的序列值每个会话，实例都不同，这样写入索引的数值打散在不同的Blocks中，并且对于会话层面申请的sequence值连续，
对写入的速度没有降低，减少了索引块争用，提高了并发性能！

 

2.2 test

session 1
scalable sequence number = 6 digit scalable sequence offset number || normal sequence number
CREATE SEQUENCE enmo_seq INCREMENT BY 1 MAXVALUE 100 SCALE EXTEND;
SQL> SELECT enmo_seq.nextval FROM dual;
   NEXTVAL
----------
 101149001
 
SQL> select USERENV('INSTANCE'),USERENV('SID') from dual;
USERENV('INSTANCE') USERENV('SID')
------------------- --------------
                  1            149
                  
SELECT enmo_seq.nextval FROM dual;
   NEXTVAL
----------
 101149007
                  
session 2
SQL> SELECT enmo_seq.nextval FROM dual;
   NEXTVAL
----------
 101078003
·····
SQL>SELECT enmo_seq.nextval FROM dual;
   NEXTVAL
----------
 101078006

可以通过上述观察到，前六位与官方文档说的算法符合！  后面就是和普通序列一样了

 

达到MAX上限报错

SQL> select * from USER_SEQUENCES;
SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S S K
------------------------------ ---------- ---------- ------------ - - ---------- ----------- - - - - -
ENMO_SEQ                                1        100            1 N N         20          21 Y Y N N N
SCALE_FLAG  指示序列是否为可伸缩序列
EXTEND_FLAG 指示可伸缩序列是否可扩展，即是否将EXTEND选项应用于可伸缩序列，以便序列值可以扩展到为所指定的值之外MAXVALUE：



alter SEQUENCE enmo_seq MAXVALUE 10;
SELECT enmo_seq.nextval FROM dual
   NEXTVAL
----------
  10114910

SQL> r
  1* SELECT enmo_seq.nextval FROM dual
SELECT enmo_seq.nextval FROM dual
*
ERROR at line 1:
ORA-08004: sequence ENMO_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

NOEXTEND与EXTEND的区别, NOEXTEND序列多少位数,占用多少位的字符，EXTEND会将MAXVALUE的位数占满！  默认NOEXTEND

CREATE SEQUENCE enmo_seq3 INCREMENT BY 1 MAXVALUE 12345678 SCALE NOEXTEND;
SELECT enmo_seq3.nextval FROM dual;
   NEXTVAL
----------
  10114902

CREATE SEQUENCE enmo_seq4 INCREMENT BY 1 MAXVALUE 12345678 SCALE EXTEND;
SELECT enmo_seq4.nextval FROM dual;
              NEXTVAL
---------------------
       10114900000003

官方文档的举例

EXTEND option

   6 digit scalable sequence offset number || 001
   6 digit scalable sequence offset number || 002
   6 digit scalable sequence offset number || 003
   ...
   6 digit scalable sequence offset number || 100

NOEXTEND option

When the NOEXTEND option is specified for the SCALE clause, which is the default option, the number of scalable sequence digits cannot exceed the number of digits specified in the MAXVALUE clause

For example, for an ascending scalable sequence with MINVALUE of 1, MAXVALUE of 1000000 (7 digits), and NOEXTEND option specified, the scalable sequence values will be of 7 digits, because MAXVALUE of 1000000 contains 7 digits, and will be of the form:

   6 digit scalable sequence offset number || 1
   6 digit scalable sequence offset number || 2
   6 digit scalable sequence offset number || 3
   ...
   6 digit scalable sequence offset number || 9