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

13 comments:

  1. equence as default to column is a good feature. If you cache sequence in 1000 then you may get even improved performance.
    Identity is available in other databases for many versions and it was always in my wish list for Oracle to implement.
    Thanks for sharing this.

    Devang Shah
    Database Architect

    ReplyDelete
  2. Personally, I like sequences better the auto increment columns. I like being able to see where the sequence numbers are in the database especially if you have do a natural key. Personally, I like artificial keys, enforces uniqueness through a index.

    ReplyDelete
  3. SQL> create or replace trigger trg_sequence_test
    before insert on sequence_test
    for each row
    begin
    If :new.id is null then
    :new.id := seq_sequence_test.nextval;
    end if;
    end;
    /

    This soluction allows inform or not the key in the INSERT commands.

    ReplyDelete
  4. so Oracle discovered in 12c the identity with auto-increment that MS SQL has at least SQL 2000 :) it was obvious that 1 mil.+ calls of trigger and sequence would take quite some time


    George Novac
    Head of BI Office @ Cosmote RMT SA

    ReplyDelete
  5. Sequence is an old and reliable approach, but in multiuser apps due to caching there maybe spans between sequential values. For example, when application fails and must be reconnected to database then all cached values are missed. It's interesting for me what behaivour of Oracle 12c identity column in such situations? The performance about the same as for sequence, what is a benefit?

    ReplyDelete
    Replies
    1. oh, there is a performance gain. sequence+trigger based implementation took 02 minutes 44 seconds and IDENTITY took only 01 minute 52 seconds in my demo.

      Delete
  6. I have used both Ss and oracle over the years, and this was one of only a handful of things oracle lacked, so it's great it now has it. However I don't follow why a sequence would have ever been called in a trigger, for a million rows, from what I gather is a loop with an after insert tigger. if doing bulk inserts just insert it as a value, all at once. I have more than likely not understood the context.

    ~Andrew Bennett

    ReplyDelete
  7. after over 10 years of Oracle experience, a year before, I had been working on Sql Server and found many cute stuffs... ease Temporary table, Auto increment. However still hard to rely on @@identity for batch processing while numbers of concurrent session using the same identity... SS mess it up

    ReplyDelete
  8. All you SQL Server lovers can only speak about this, which is bottom line not a problem if you are not lazy enough to write 2-3 lines of code. For the rest, SQL server eats the dust from Oracle....and worst, you know it!!!!!! :)

    ReplyDelete
  9. Would like to share A Great Advice by Steven Feuerstein on using sequences with triggers
    Me: direct sequence assignment does the same behind the scene, so I don't mind selecting nextval from dual.
    SF: U might not mind, but developers in future could, when/if @OracleDatabase changes implementation and avoids select from dual.
    SF: Always choose the highest level most declarative implementation possible!

    ReplyDelete
  10. Spot on with this write-up, I honestly feel this amazing site needs a
    great deal more attention. I'll probably be returning to see more, thanks
    for the info!

    ReplyDelete