Simon Krenger

With Oracle 12c, Oracle introduced a new way to insert primary keys into tables: The GENERATED BY DEFAULT and DEFAULT keywords.

In this post, I want to compare the performance of different methods to insert a primary key into a table in Oracle. The comparison will also include older methods, such as using a trigger or specifying the primary key in the INSERT statement. These methods can also be used in Oracle versions earlier than 12c.

First of all, let’s look at the possible ways to insert the value of a sequence into a table:

• Trigger
• Sequence with SELECT
• Sequence as DEFAULT
• Sequence GENERATED BY DEFAULT

In the following chapters, I will discuss these methods and point out their advantages and disadvantages.

Trigger

If you want to insert a primary key into a table whenever there is a new record, one of the first methods you come across is to use a trigger. For this method, we create a TRIGGER ... BEFORE INSERT ... FOR EACH ROW. Whenever the trigger fires, we read the latest value from our sequence and insert it into our table:

CREATE SEQUENCE test_trigger_seq;
CREATE TABLE test_trigger (field1 NUMBER, field2 NUMBER);
CREATE OR REPLACE TRIGGER TR_TEST_trigger_PK
  BEFORE INSERT
  ON TEST_TRIGGER FOR EACH ROW
DECLARE
  BEGIN
    select TEST_TRIGGER_SEQ.nextval into :new.field1 from dual;
  END;

To test this trigger, we are going to insert 1’000’000 rows into the table:

BEGIN
FOR simon IN 1..1000000
	LOOP
		INSERT INTO test_trigger (field2) VALUES (simon);
	END LOOP;
END;
/

Elapsed: 00:01:29.62

This takes us around one and a half mintes. As we will discover, this is the worst way to insert a primary key into a table.

Sequence with SELECT

The next possibility is to define the sequence as the INSERT value, which is what Oracle recommends to use up to Oracle 11g. However, it is susceptible to inserting any value we want, as we do not necessarily have to use the sequence! The code is quite simple:

CREATE SEQUENCE test_trigger_sel;
CREATE TABLE test_by_select (field1 NUMBER,field2 NUMBER);

For our test, we insert one million rows once more:

BEGIN FOR simon IN 1..1000000
	LOOP
		INSERT INTO test_by_select (field1,field2) VALUES (test_trigger_sel.nextval,simon);
	END LOOP;
END;
/

Elapsed: 00:00:47.23

Whoa, quite fast! This method is nearly twice as fast as the method using a trigger. So this method is what I would recommend using in a program. However, you’ll need to be strict about using the sequence to generate the primary key.

Sequence as DEFAULT

With Oracle 12c, we are now able to specify a sequence as the DEFAULT value while defining a table:

CREATE SEQUENCE test_default_seq;
CREATE TABLE test_default (
field1 NUMBER DEFAULT test_default_seq.nextval,
field2 NUMBER);

Note that this will do exactly what we want, but we are still able to overwrite this value if we are not careful. When we look at the performance, we notice that this is as fast as the method above (using the sequence as the INSERT value):

BEGIN
FOR simon IN 1..1000000
	LOOP
		INSERT INTO test_default (field2) VALUES (simon);
	END LOOP;
END;
/

Elapsed: 00:00:47.41

Sequence GENERATED ALWAYS

Also with Oracle 12c, we are now able to use an identity clause to define a column as identity. Note that we do not need to create a seperate sequence and just tell Oracle to always generate this value from a sequence (which will be created implicitly). Oracle Database always uses the sequence generator to assign a value to the column. If you attempt to explicitly assign a value to the column using INSERT or UPDATE, then an error will be returned.

CREATE TABLE test_always (
field1 number GENERATED ALWAYS AS IDENTITY,
field2 number);

The implicitly generated sequence has some issues, as discussed here for example. When we then insert another 1’000’000 rows, we find that this also takes us around 47 seconds:

BEGIN
FOR simon IN 1..1000000
	LOOP
		INSERT INTO test_always (field2) VALUES (simon);
	END LOOP;
END;
/

Elapsed: 00:00:47.14

If you are using Oracle 12c, I would probably recommend to use this method to insert a primary key. However, you will not be able to run your software with an older version of Oracle.

Sequence GENERATED BY DEFAULT

Another new method introduced with Oracle 12c is to use the DEFAULT AS IDENTITY clause. This is a bit of a laxer method than GENERATED ALWAYS discussed above. Unless you really need it, I would discourage using this method.

CREATE TABLE test_by_default (
field1 NUMBER GENERATED BY DEFAULT AS IDENTITY,
field2 NUMBER);

We find that this also executes quite quickly:

BEGIN
FOR simon IN 1..1000000
	LOOP
		INSERT INTO test_by_default (field2) VALUES (simon);
	END LOOP;
END;
/

Elapsed: 00:00:47.97

Results

Now we have seen different methods to insert a primary key into a table. Let’s summarize these findings into a table to see the differences:

Variant	Time	From version
TRIGGER	1m 30s	11g
SELECT sequence (INSERT)	47s	11g
Sequence as DEFAULT value	47s	12c
GENERATED ALWAYS	47s	12c
GENERATED ON DEFAULT	47s	12c

So what does this result tell us? You should definitely no longer use Oracle triggers to insert your primary key! Even as of Oracle 11g you should use the “SELECT sequence (INSERT)” method to insert your primary keys into a table instead of a trigger. If you are using Oracle 12c, then the GENERATED ALWAYS clause is probably the best way to insert a primary key into a table.