Sequence triggers and column defaults

Tables with simple sequence assigned ID columns for primary keys are quite common. Unfortunately it also seems quite common to use row level triggers to assign those sequence values to the columns. The rationale trotted out for this strategy is that it doesn’t matter what the application layer does, the database will take case of ensuring that the ID is unique and it can’t be bypassed.

The primary problem with using a trigger to assign a sequence value to an ID column is one of performance. Let’s look at a simple test case that shows the overhead incurred in using a trigger to assign the ID column value, followed by a new feature from Oracle 12c that achieves the same end but without the overhead of a trigger.

Our test is to insert 1,000,000 rows into a table. We’ll run through the scenarios of:

• ID value is not sequence generated – this is our baseline scenario to show how fast Oracle can copy rows
• ID value is sequence assigned as part of the insert statement
• ID value is sequence assigned from a trigger
• ID value is sequence assigned from a column level default – this is the new 12c feature

The set-up is to create the table we will be testing against, TEST_TABLE, and also a source data table, SOURCE_DATA, which we’ll load with 1,000,000 rows. Each test is to copy the data from SOURCE_DATA to TEST_TABLE. The tests were all conducted on Oracle 12.1.0.1.

CREATE TABLE test_table
   (id      NUMBER(10) NOT NULL
   ,padding CHAR(20)  NOT NULL)
/

CREATE UNIQUE INDEX test_table_pk
   ON test_table (id)
/

ALTER TABLE test_table
   ADD CONSTRAINT test_table_pk
   PRIMARY KEY (id)
/   

CREATE TABLE source_data
   (id, padding)
PCTFREE 0   
AS
SELECT ROWNUM AS id
,      'X' AS padding
FROM   dual
CONNECT BY ROWNUM <= 1000000
/

COMMIT
/

First of all we time how long it takes to copy across the 1,000,000 rows in SOURCE_DATA to TEST_TABLE:

SQL>SET TIMING ON

SQL>INSERT INTO test_table
  2  SELECT *
  3  FROM   source_data
  4  /

1000000 rows created.

Elapsed: 00:00:03.06

Now we measure how long it takes to copy across the rows, assigning ID values from a sequence directly from within the INSERT statement:

SQL>TRUNCATE TABLE test_table
  2  /

Table truncated.

SQL>CREATE SEQUENCE test_table_id_seq
  2     CACHE 1000
  3  /

Sequence created.

SQL>SET TIMING ON

SQL>INSERT INTO test_table
  2  SELECT test_table_id_seq.NEXTVAL
  3  ,      padding
  4  FROM   source_data
  5  /

1000000 rows created.

Elapsed: 00:00:09.68

The insert of the rows went from 3 seconds to just under 10 seconds simply by assigning new ID values from a sequence (the cache size for the sequence was 1,000).

Let’s see what happens if we use a trigger to assign the sequence value to the ID column:

SQL>TRUNCATE TABLE test_table
  2  /

Table truncated.

SQL>CREATE OR REPLACE TRIGGER trg_test_table
  2     BEFORE INSERT ON test_table
  3     FOR EACH ROW
  4  DECLARE
  5  BEGIN
  6     :NEW.id := test_table_id_seq.NEXTVAL;
  7  END trg_test_table;
  8  /

Trigger created.

SQL>SET TIMING ON

SQL>INSERT INTO test_table
  2     (padding)
  3  SELECT padding
  4  FROM   source_data
  5  /

1000000 rows created.

Elapsed: 00:01:29.65

Yikes!!! With the trigger in place it took 1.5 minutes to insert the rows! This emphasises a really important aspect of triggers: they’re performance killers!

So, you have a system that is littered with triggers doing nothing more than assign sequence values to the ID columns… what can you do? Well, in Oracle 12c there’s a new feature that can come to your rescue. Column defaults can now reference sequence NEXTVAL and CURRVAL. Let’s see how this works:

SQL>TRUNCATE TABLE test_table
  2  /

Table truncated.

SQL>DROP TRIGGER trg_test_table
  2  /

Trigger dropped.

SQL>ALTER TABLE test_table
  2    MODIFY id DEFAULT test_table_id_seq.NEXTVAL
  3  /

Table altered.

SQL>SET TIMING ON

SQL>INSERT INTO test_table
  2     (padding)
  3  SELECT padding
  4  FROM   source_data
  5  /

1000000 rows created.

Elapsed: 00:00:07.04

From the above you can see that we’ve added a default to the ID column that is the sequence NEXTVAL. Our timing for the insert is now down to 7 seconds, which is pretty much the same as we got when calling the sequence directly in the insert statement. We’ve achieved the outcome of the trigger with the performance of the direct insert!

However, if you’re thinking “Great, I can get rid of my triggers and replace them with column defaults!” be a little bit careful though. While the trigger and default perform the same function there is a slight difference in them; a trigger might always set the ID column to a new value even if one is explicitly supplied by the application code whereas the default will only set the ID value if one is not supplied. Consider the following, first with the trigger in place:

SQL>CREATE OR REPLACE TRIGGER trg_test_table
  2     BEFORE INSERT ON test_table
  3     FOR EACH ROW
  4  DECLARE
  5  BEGIN
  6     :NEW.id := test_table_id_seq.NEXTVAL;
  7  END trg_test_table;
  8  /

Trigger created.

SQL>INSERT INTO test_table
  2  VALUES (0, 'X')
  3  /

1 row created.

SQL>INSERT INTO test_table
  2  VALUES (0, 'X')
  3  /

1 row created.

Even though the INSERT explicitly provided a value for the ID column it was overridden by the sequence value assigned by the trigger. Contrast that to what would happen with a column default:

SQL>DROP TRIGGER trg_test_table
  2  /

Trigger dropped.

SQL>ALTER TABLE test_table
  2    MODIFY id DEFAULT test_table_id_seq.NEXTVAL
  3  /

Table altered.

SQL>INSERT INTO test_table
  2  VALUES (0, 'X')
  3  /

1 row created.

SQL>INSERT INTO test_table
  2  VALUES (0, 'X')
  3  /
INSERT INTO test_table
*
ERROR at line 1:
ORA-00001: unique constraint (DEVELOPER.TEST_TABLE_PK) violated

Since an ID value was specified the column default is not used. Hopefully you do not have any code that seeks to explicitly set a column value only to be overridden by a trigger assigned value as that would be illogical and confusing. Chances are you will quickly catch this sort of a problem in your test environment if you do so choose to replace your triggers with column defaults.

Advertisement