Starting 18.1 database, “Scalable Sequences” have been introduced. The ability to create Scalable Sequences has been added to improve the performance of data loading into tables having sequence values as keys. This feature provides the option to add instance and session offsets to significantly reduce the possibility of sequence and index block contention when loading data across RAC instances and multiple loading processes within single instances.
The new business benefit is that it further enhances the Oracle Database data loading capabilities by reducing contention when loading data into tables that use sequence values as keys. By adding the ability to create sequences with the instance and session ids added to the sequence value, contention on sequence value generation and index blocks insert for the key values is significantly reduced. This means that Oracle Database is even more scalable for data loading and can support even higher data loading rates.
SQL Statement
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}
]
SCALE/NOSCALE
When SCALE is specified, a numeric offset is affixed to the beginning of the sequence. This offset is 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.
The addition of 100 in generating iii ensures that all generated values are of the same length, and consequently, there will be no duplicates in the generated values across all instances.
The default length of the scalable sequence offset is 6.
Example of EXTEND/NOEXTEND option
Let us see an examples of creating a scalable sequence with EXTEND/NOEXTEND option of the SCALE keyword.
SQL> select instance_number from v$instance; INSTANCE_NUMBER --------------- 1
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
------------------------------------------------------------------------------------------------------------------------------------
22
EXTEND
SQL> create sequence seq_extend start with 1 increment by 1 minvalue 1 maxvalue 100 scale extend; Sequence created.
SQL> select seq_extend.nextval from dual; NEXTVAL ---------- 101022001
NOEXTEND
SQL> create sequence seq_noextend start with 1 increment by 1 minvalue 1 maxvalue 100 scale noextend; Sequence created.
SQL> select seq_noextend.nextval from dual; select seq_noextend.nextval from dual * ERROR at line 1: ORA-64603: NEXTVAL cannot be instantiated for SEQ_NOEXTEND. Widen the sequence by 4 digits or alter sequence with SCALE EXTEND.