From Oracle 18c, Sequences can be made scalable by using SCALE clause in CREATE/ ALTER SEQUENCE statement. Scalable sequences optimize the sequence generation by using a unique combination of instance number and session number to reduce the impact of Index leaf block contention during massive loads. This is  one of the few features that is not automatically enabled as it requires some intervention by DBA’s to ensure this does not change their implemented business logic.

Here is the syntax for defining a scalable sequence.

Create /Alter  sequence sequence_name …… scale [extend | noextend] | noscale

You can check DBA_SEQUENCES/USER_SEQUENCE/ALL_SEQUENCE dictionaries to know whether sequence is scalable or not. By default sequences are not scalable as you can see in dictionary both columns scale_flag/extend_flag is set to N.

Scale:

Now create the sequence with scale clause. When the scale clause is specified, a 6 digit numeric scalable number is prefixed to the digit of the sequence. Out of 6 digits, three digits  are instance numbers followed by a three digit session number. These are generated by: Instance number is [(instance id % 100) + 100] Session number is [session id % 1000]

Now change the maxvalue of sequence. 7 digit maxvalue will generate a sequence with 7 digits only.

The NEXTVAL operations on this sequence will generate sequence numbers till 6 digit scalable offset number || 9 and after that it’ll report an error as shown below. why??? Because 6 digit scalable sequence || 10 will contain 8 digits which is greater than maxvalue.

You can use this sequence again if you change the 8 digit maxvalue value.

Extend:

Noextend is default option and now specify the extend clause along with scale. with extend option, the database add the 6digit scale on top of the digit for the maxvalue. So in below example, the scalable sequence will be 13 digits ( 6 digit scalable offset number + 7 digit maxvalue).

Noscale:

You can alter extend and noextend clause. Also can change a scalable sequence to normal sequence and vice versa.

Oracle recommends that you should not specify ordering for a scalable sequence, because scalable sequence numbers are globally unordered.

Cheers

Mandy

Leave a Reply