• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Database 18c new feature – Scalable Sequences

By admin

Starting 18.1 database, “Scalable Sequences” have been introduced. The ability to create Scalable Sequences has been added to improve the performance of data loading into tables having sequence values as keys. This feature provides the option to add instance and session offsets to significantly reduce the possibility of sequence and index block contention when loading data across RAC instances and multiple loading processes within single instances.

The new business benefit is that it further enhances the Oracle Database data loading capabilities by reducing contention when loading data into tables that use sequence values as keys. By adding the ability to create sequences with the instance and session ids added to the sequence value, contention on sequence value generation and index blocks insert for the key values is significantly reduced. This means that Oracle Database is even more scalable for data loading and can support even higher data loading rates.

SQL Statement

CREATE | ALTER SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE}
]

SCALE/NOSCALE

When SCALE is specified, a numeric offset is affixed to the beginning of the sequence. This offset is of the form “iii||sss||“,

where,
iii denotes a three digit instance offset given by (instance_id % 100) + 100,
sss denotes a three digits session offset given by (session_id % 1000), and
|| is the concatenation operator

EXTEND/NOEXTEND

When EXTEND is specified with the SCALE keyword, the generated sequence values are all of length (x+y), where x is the length of the scalable offset (default 6), and y is the maximum number of digits in the sequence maxvalue/minvalue. Thus, for an ascending sequence with maxvalue 100 and SCALABLE EXTEND specified, the generated sequence values are of the form iii||sss||001, iii||sss||002, …,iii||sss||100

The default setting for the SCALE clause is NOEXTEND. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence maxvalue/minvalue. This setting is useful for integration with existing applications where sequences are used to populate fixed width columns. On invocation of NEXTVAL on a sequence with SCALABLE NOEXTEND specified, a user error is thrown if the generated value requires more digits of representation than the sequence’s maxvalue/minvalue.

The addition of 100 in generating iii ensures that all generated values are of the same length, and consequently, there will be no duplicates in the generated values across all instances.

The default length of the scalable sequence offset is 6.

Example of EXTEND/NOEXTEND option

Let us see an examples of creating a scalable sequence with EXTEND/NOEXTEND option of the SCALE keyword.

SQL> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
1
SQL> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
------------------------------------------------------------------------------------------------------------------------------------
22

EXTEND

SQL> create sequence seq_extend start with 1 increment by 1 minvalue 1 maxvalue 100 scale extend;

Sequence created.
SQL> select seq_extend.nextval from dual;

NEXTVAL
----------
101022001

NOEXTEND

SQL> create sequence seq_noextend start with 1 increment by 1 minvalue 1 maxvalue 100 scale noextend;

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

Filed Under: 18c, oracle

Some more articles you might also be interested in …

  1. How to Rename or Move Datafiles and Logfiles in Oracle Database
  2. How to move ASM spfile to a shared device in RAC
  3. Oracle Database – Measuring Network Capacity using oratcptest
  4. How to Turn Archiving ON and OFF in Oracle database
  5. How to Drop/Truncate Multiple Partitions in Oracle 12C
  6. How to get the Values Assigned by Default to a Profile in Oracle Database
  7. How to recreate an ASM disk group
  8. Unable to create spfile for Oracle ASM instance
  9. Why Can I Login AS SYSDBA With Any Username and Password
  10. Common Init.ora Parameters and Unix, Linux Kernel Parameters and Relationship Between Them

You May Also Like

Primary Sidebar

Recent Posts

  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • How to Capture More Logs in /var/log/dmesg for CentOS/RHEL
  • Unable to Start RDMA Services on CentOS/RHEL 7
  • How to rename a KVM VM with virsh
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary