Oracle 12c introduced a new featured called
identity column which allows auto population of primary key columns, perfect for database models that use surrogate keys. Before the identity column was introduced same was achieved with the use of trigger and sequence (identity column also use a sequence behind the scene). This post is to compare the two methods and see if there's any performance regression or benefits of using one over the other.
Two tables were created first one for the trigger base approach.
SQL> create table seqpritable (a number primary key, b number, c varchar2(100));
SQL> create sequence IDSEQ cache 100 noorder;
Create Or Replace Trigger Seqinstrig Before Insert On Seqpritable REFERENCING NEW AS NEW OLD AS OLD for each row
Begin
select idseq.nextval into :NEW.a from dual;
end;
/
The second table with identity column
SQL> create table seqnotrigtable (a number generated as identity cache 100 noorder primary key, b number, c varchar2(100));
Looking at the sequence it could seen the system generated sequence for the identity column
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER PARTITION_COUNT S K
--------------- ---------- ---------- ------------ - - ---------- ----------- --------------- - -
IDSEQ 1 1.0000E+28 1 N N 100 1 N N
ISEQ$$_26555 1 1.0000E+28 1 N N 100 1 N N
A multi-threaded java code was used to insert some rows into the tables (code is given at the end of the post). The testing was done on a 2 node RAC running Oracle 12 SE2 (12.1.0.2.160419). Two sets of tests were carried out, first 2 concurrent threads inserting and on second test 4 concurrent threads were inserting. The
active session usage is used for comparison and is shown for the tests below (first two spikes refer to 2 threads inserting and second 2 spikes refer to 4 threads inserting).
The cluster (gc current block busy) and other (gcs log flush sync) are present on all the test same level and not related to use of sequence but on insert of data to the table. In terms of CPU usage there's not much difference either. Looking at this test result it's clear that no method is beneficial over the other in terms of resource usage/performance. However in terms of administration and management the identity column tops as there's no need to maintain a separate sequence or trigger. So when migrating to 12c it may be a worthwhile to move trigger based surrogate key population to identity columns and take advantage of this feature.
Java code used for inserts
public class SeqInsert extends Thread {
private Connection con;
public SeqInsert(Connection con) {
this.con = con;
}
public static void main(String[] args) {
try {
OracleDataSource pool = new OracleDataSource();
pool.setURL("jdbc:oracle:thin:@rac-scan.domain.net:1521/std12csrv");
pool.setUser("asanga");
pool.setPassword("asa");
SeqInsert[] ts = new SeqInsert[Integer.parseInt(args[0])];
for(int i = 0 ; i < ts.length; i++){
Connection con = pool.getConnection();
con.setAutoCommit(false);
ts[i] = new SeqInsert(con);
}
for(int i = 0 ; i < ts.length; i++){
ts[i].start();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
@Override
public void run() {
try {
// String SQL = "insert into seqpritable (b,c) values (?,?)";
String SQL = "insert into seqnotrigtable (b,c) values (?,?)";
PreparedStatement pr = con.prepareStatement(SQL);
for (int i = 0; i < 10000; i++) {
pr.setInt(1, i);
pr.setString(2, getName()+ i);
pr.execute();
}
con.commit();
pr.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
