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:
- 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.
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
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:
|SELECT sequence (INSERT)||47s||11g|
|Sequence as DEFAULT value||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.