jump to navigation

18c Scalable Sequences Part III (Too Much Rope) May 8, 2018

Posted by Richard Foote in 18c, 18c New Features, Oracle Indexes, Scalable Sequences.
trackback

amused to death

I previously looked in Part I and Part II how Scalable Sequences officially released in 18c can reduce index contention issues, by automatically assigning a 6 digit prefix to the sequence value based on the instance ID and session ID of the session.

We need to be careful and consider this 6 digit prefix if we decide to set a maxvalue, which we might consider doing as the default Scalable Sequence value is quite large as I showed previously:

SQL> create sequence ziggy_seq scale;

Sequence created.

SQL> select sequence_name, scale_flag, extend_flag from user_sequences

where sequence_name='ZIGGY_SEQ';

SEQUENCE_NAME   SCALE_FLAG EXTEND_FLAG
--------------- ---------- ------------
ZIGGY_SEQ       Y          N

SQL> select ziggy_seq.nextval from dual;

NEXTVAL
----------------------------------
1013890000000000000000000001

Now I might consider a maximum value of say 9999 to be sufficient for my needs:

SQL> create sequence major_tom_seq maxvalue 9999 scale;

Sequence created.

SQL> select sequence_name, scale_flag, extend_flag from user_sequences

where sequence_name='MAJOR_TOM_SEQ';

SEQUENCE_NAME  SCALE_FLAG EXTEND_FLAG
-------------- ---------- -----------
MAJOR_TOM_SEQ  Y          N

However, if I try to generate a value from my Scalable Sequence:

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

It generates a error saying that the next value can’t be instantiated. This is due to the maxvalue of 9999 not being sufficient to store the necessary 6 digit prefix, never mind the actual generated value. Therefore the maximum value of a Scalable Sequence must at least be a 7 digit number.

If I really wanted an effective sequence maxvalue of 9999 with a Scalable Sequence, I need to add the 6 digit prefix to this maximum value:

SQL> alter sequence major_tom_seq maxvalue 9999999999;

Sequence altered.

SQL> select major_tom_seq.nextval from dual;

NEXTVAL
----------
1029730001

Alternatively, I could have created (or altered) the sequence with the EXTEND clause, such that Oracle then automatically adds the necessary 6 digits to the sequence definition:

SQL> create sequence bowie_seq maxvalue 9999 scale extend;

Sequence created.

SQL> select sequence_name, scale_flag, extend_flag from user_sequences

where sequence_name='BOWIE_SEQ';

SEQUENCE_NAME  SCALE_FLAG EXTEND_FLAG
-------------- ---------- -----------
BOWIE_SEQ      Y          Y

SQL> select bowie_seq.nextval from dual;

NEXTVAL
----------
1010160001

 

Just a final note. Scalable Sequences are available in 12.1.0.1, but they were neither documented or officially supported. But you can at least play with them if you don’t have access to an 18c environment. That said, you can play with them for free on the Oracle Live SQL facility.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: