identity column in oracle

I was involved in a DB2 to Oracle 11gr2 migration.
There were couple of table in db2 that had identity columns.  These columns get incremented during inserts.
In oracle, the equivalent to this is to use sequence objects in the database.

As always, oracle gives you more control than any other database. I can think of two methods that could be used to implement identity columns.

•  Sequence only
• Sequence + trigger.

 Sequence only : In oracle you could simply create a sequence object and call  sequenceName.nextvalue in the insert statement. this is most efficient. This is the  method mostly implemented by Oracle DBAs/applications.
e.g. insert into test_tab values ( sequence.nextvalue,' testvalue'); 

Sequence + Trigger : This method is slightly more code than the first option. One advantage of this method is that your sql code could run on both oracle and DBs that have identity columns. You still have to ensure that you list all the columns you are inserting  and skip the identity column.
eg. insert into test-tab(ename) values ('testvalue');
 

In the below test, i could insert 100000 rows with no trigger in 10 seconds and 26 seconds with triggers.
The difference may be very insignificant for most of the cases.
I also found no difference in performance with the IF condition in the trigger. So it is a good to have the IF condition in place so that they do not get fired with we try to load data into the table including the column having the trigger.


SQL> @trig
SQL>
SQL> --create table trig_tab nologging parallel as select *     from testvijay;
SQL> --create sequence trig_tab_seq START WITH 1 INCREMENT BY 1 NOCYCLE cache 1000;
SQL> create     or replace trigger trig_tab_tr
  2  BEFORE INSERT ON trig_tab
  3  FOR EACH ROW
  4  BEGIN
  5      IF :new.object_id IS null THEN
  6          SELECT trig_tab_seq.nextval INTO :new.object_id FROM DUAL;
  7      END IF;
  8  END disable ;
  9  /

Trigger created.

Elapsed: 00:00:00.12
SQL> alter trigger trig_tab_tr enable;

Trigger altered.

Elapsed: 00:00:00.02
SQL> set timing on
SQL> set echo on
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:26.26
SQL> commit;

Commit complete.

Elapsed: 00:00:00.95
SQL> create     or replace trigger trig_tab_tr
  2  BEFORE INSERT ON trig_tab
  3  FOR EACH ROW
  4  BEGIN
  5   --   IF :new.object_id IS null THEN
  6          SELECT trig_tab_seq.nextval INTO :new.object_id FROM DUAL;
  7    --  END IF;
  8  END disable ;
  9  /

Trigger created.

Elapsed: 00:00:00.13
SQL> alter trigger trig_tab_tr enable;

Trigger altered.

Elapsed: 00:00:00.03
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:26.23
SQL> commit;

Commit complete.

Elapsed: 00:00:04.80
SQL> alter trigger trig_tab_tr disable;

Trigger altered.

Elapsed: 00:00:00.09
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,object_id,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,trig_tab_seq.nextval,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:10.08
SQL> commit;

Commit complete.