Auto Increment Column Performance Enhancement with each Oracle Version

With Oracle 10g to Oracle 11g and now in Oracle 12c, there were enhancements made on how to maintain an Auto Increment Column (mostly primary key) with sequence, trigger and identity column. With this post I am trying to compare the performance enhancement with every Oracle Version for maintaining a Auto Increment Primary Key Column. I will be focusing on following methods in this post
1) Sequence value assignment using SQL (Oracle 10g or earlier)
2) Direct Sequence Assignment in PL/SQL (Oracle 11g)
3) Sequence as default value (Oracle 12c)
4) Generated always as identity (Oracle 12c)

1) Oracle 10g or earlier versions, Sequence value assignment using SQL.

SQL> create table sequence_test
  2  (
  3     id number,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> create sequence seq_sequence_test start with 1 increment by 1 nocache nocycle;
Sequence created.

SQL> create or replace trigger trg_sequence_test
  2  before insert on sequence_test
  3  for each row
  4  begin
  5      select seq_sequence_test.nextval into :new.id
  6      from dual;
  7  end;
  8  /
Trigger created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050168 rows created.

Elapsed: 00:02:48.30

Took 2 minutes and 48 seconds to insert little over 1 Million records. Lets check the performance for the enhancement done for Oracle 11g.


2) Oracle 11g, Direct Sequence Assignment

SQL> drop table sequence_test purge;
Table dropped.

SQL> drop sequence seq_sequence_test;
Sequence dropped.

SQL> create table sequence_test
  2  (
  3     id number,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> create sequence seq_sequence_test start with 1 increment by 1 nocache nocycle;
Sequence created.

SQL> create or replace trigger trg_sequence_test
  2  before insert on sequence_test
  3  for each row
  4  begin
  5     :new.id := seq_sequence_test.nextval;
  6  end;
  7  /
Trigger created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050168 rows created.

Elapsed: 00:02:44.60

As we can notice, the executing time is almost same as of Oracle 10g method. Oracle 11g direct sequence assignment provides simplicity but not the performance as Oracle has not changed the implementation of fetching value from sequence.


3) Oracle 12c, Sequence as default value

SQL> drop table sequence_test purge;
Table dropped.

SQL> drop sequence seq_sequence_test;
Sequence dropped.

SQL> create sequence seq_sequence_test start with 1 increment by 1 nocache nocycle;
Sequence created.

SQL> create table sequence_test
  2  (
  3     id number default seq_sequence_test.nextval,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050112 rows created.

Elapsed: 00:01:53.47

With Oracle 12c enhancements Sequence as Default Value, there is a lot of improvement with sequence performance and we no longer need to use triggers for surrogate primary key and by this we can save the expensive context switch. But it is little risky as someone can overwrite the sequence value if pass any value in primary key column.


4) Oracle 12c, GENERATED ALWAYS AS IDENTITY (My Personal Favorite)

SQL> drop table sequence_test purge;
Table dropped.

SQL> drop sequence seq_sequence_test;
Sequence dropped.

SQL> create table sequence_test
  2  (
  3     id number GENERATED ALWAYS AS IDENTITY,
  4     object_name varchar2(128),
  5     constraint pk_sequence_test primary key (id)
  6  );
Table created.

SQL> insert into sequence_test(object_name)
  2  select object_name from dba_objects, scott.emp, scott.dept;
1050112 rows created.

Elapsed: 00:01:52.69

It is almost same as Sequence as default value of Oracle 12c but if we compare it with Oracle 11g or earlier, we can see alot of performance gain. Benefit of GENERATED ALWAYS AS IDENTITY are
a) we do not create sequence (oracle will do it for us)
b) Oracle database will always use sequence value and will throw an error if you attempt to explicitly assign a value to the column using INSERT or UPDATE.
c) performance is highly improved over Oracle 11g or prior methods


I hope that you have enjoyed reading this post. Please comment your feedback and suggestions.

Related Posts:
- Sequence Behavior with Multitable Insert All
- Oracle Database 12c New Features for Developers
- Top 15 new features of Oracle Database 12.2 for developers
- Oracle Auto Increment Column - Sequence as Default Value
- Setting Sequence Value to a Specific Number
- Sequence: NEXTVAL, CURRVAL and SESSION
- USER_SEQUENCES.LAST_NUMBER AND SEQUENCE CACHE
- Complex View Merging Transformation and ORA-00979 in Oracle 12c