Hi,
This idea is about something that exists already but is not documented - so we can't use it. The idea is to make it available.
Populating a primary key from sequence can be bad for scalability because all new (and concurrent) inserts are updating the same index block. Reverse indexes is not a very good idea because it spreads the index updates on all leaf blocks, which is too much to be kept in cache. Hash partitioning the index is better, but still show contention in RAC because several instances are updating the same set of blocks.
A good solution is to prefix the number coming from the sequence by something that is determined by the session. Or have a composite primary key with first column coming from a hash of the instance+session number and the second one from the sequence.
This is possible only when the application code can be changed and nowadays some developers and frameworks don't like composite keys.
The idea is to have sequences returning sequences is several ranges of values.
For example, one session selecting the nextval will get:
20000000000000000000000000001
20000000000000000000000000002
20000000000000000000000000003
20000000000000000000000000004
20000000000000000000000000005
20000000000000000000000000006
then another session will get:
10000000000000000000000000011
10000000000000000000000000012
another one:
00000000000000000000000000013
00000000000000000000000000014
and another one:
20000000000000000000000000015
20000000000000000000000000016
The number of hash values would be defined as a sequence attribute, to be set according to the expected number of sessions concurrently inserting.
The prefix should be hashed from instance number (to avoid for GC contention) and from session or process number (to avoid buffer contention).
More details in the following blog post about the undocumented 12c feature that does something like that:
Oracle Partitioned Sequences - a future new feature in 12c? - dbi services Blog
Regards,
Franck.
This has been implemented with Oracle Database 18c: 24.2.4.3 Making a Sequence Scalable