oracle 18C 新增加的 scale sequence

郑全

Starting with Oracle Database 18c, you can use scalable sequences to provide betterdata load scalability instead of configuring a very large sequence cache. Scalablesequences improve the performance of concurrent data load operations, especially whenthe sequence values are used for populating primary key columns of tables.

• If you use indexes, then consider alternatives, such as reverse key indexes to optimizeindex performance.
Reverse key indexes are especially helpful if you have frequentinserts to one side of an index, such as indexes that are based on insert date.



Scalable SequencesScalable sequences alleviate index leaf block contention when loading data into tables thatuse sequence values as keys.


语法：
CREATE | ALTER SEQUENCE sequence_name ... SCALE [EXTEND | NOEXTEND] | NOSCALE

When the SCALE clause is specified, a 6 digit numeric scalable sequence offset number isprefixed to the digits of the sequence:

scalable sequence number = 6 digit scalable sequence offset number ||normal sequence numberwhere,

• || is the concatenation operator.
• 6 digit scalable sequence offset number = 3 digit instance offset number || 3 digitsession offset number.
The 3 digit instance offset number is generated as [(instance id % 100) + 100].

The 3digit session offset number is generated as [session id % 1000].

Additionally, you can also specify EXTEND or NOEXTEND option for the SCALE clause: