8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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 properly. The %_SEQUENCS views included the SCALE_FLAG and EXTEND_FLAG columns, but the syntax to use scalable sequences was not documented until 18c, and therefore they were not supported. Their usage was 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

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.

column scale_seq format 99999999999999999

drop sequence my_scale_seq;

create sequence my_scale_seq
  minvalue 1
  maxvalue 9999999999
  noscale;


SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
                 1

SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
                 2

SQL>

Setting the SCALE attribute, which is the equivalent of using SCALE NOEXTEND, causes a 6 digit prefix to be concatenated to the sequence, with padding to make it use the maximum number of digits based on the MAXVALUE value. The following example only pads with zeros to make the sequence value 10 digits long.

drop sequence my_scale_seq;

create sequence my_scale_seq
  minvalue 1
  maxvalue 9999999999
  scale;


SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
        1012800001

SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
        1012800002

SQL>

Adding the EXTEND keyword causes the sequence number to be left padded with zeros to its maximum length, then the prefix concatenated, so the final sequence number has 6 more digits than would be suggested by the MAXVALUE setting.

drop sequence my_scale_seq;

create sequence my_scale_seq
  minvalue 1
  maxvalue 9999999999
  scale extend;


SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
  1012800000000001

SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
  1012800000000002

SQL>

We can see the impact of connecting to a new session on the prefix. Notice the prefix changes after each connection.

SQL> conn test/test@pdb1
Connected.
SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
  1010200000000003

SQL> CONN test/test@pdb1
Connected.
SQL> select my_scale_seq.nextval as scale_seq from dual;

         SCALE_SEQ
------------------
  1012760000000004

SQL>

Altering Scalable Sequences

The scale and extend attributes of an existing sequence can be altered as follows.

alter sequence my_scale_seq noscale;
alter sequence my_scale_seq scale noextend;
alter sequence my_scale_seq scale extend;

Views

The %_SEQUENCES views have been extended to include the SCALE_FLAG and EXTEND_FLAG columns.

column sequence_name format a30
column scale_flag format a10
column extend_flag format a11

select sequence_name,
       scale_flag,
       extend_flag
from   user_sequences
where  sequence_name = 'MY_SCALE_SEQ';

SEQUENCE_NAME                  SCALE_FLAG EXTEND_FLAG
------------------------------ ---------- -----------
MY_SCALE_SEQ                   Y          Y

SQL>

Implications

Using scalable sequences has some obvious implications.

All sequence numbers are full size in terms of the number of digits used. If your application displays sequence numbers you may have some formatting issues you weren't expecting for a few years.
When using NOEXTEND you are effectively reducing the maximum sequence number available, as 6 digits are "lost" because of the prefix.
When you are using EXTEND you need to be able to cope with the "LENGTH(MAXVALUE)+6" digits, so variables and table columns need to be sized correctly.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.