Using DEFAULT clause for populating Surrogate Keys

In Oracle Database 12c, a suite of improvements has been implemented around the DEFAULT clause for table columns. A sequence value can now be assigned as the default value for a column.

With one simple command, we can remove the need for triggers for populating Surrogate Keys.

Creating a DB Trigger:

SQL> create or replace

  2  trigger TRG_SALES_SURROGATE_KEY

  3  before insert on SALES

  4  for each row

  5  begin

  6    :new.SALE_SEQ := SEQ_SALES.nextval;

  7  end;

  8  /

Trigger created.

Inserting 10 million rows for testing the Insert time.

SQL> insert /*+ APPEND */ into SALES

  2  select rownum, ...

  3  from

  4   ( select 1 from dual connect by level <= 10000 ),

  5   ( select 1 from dual connect by level <= 1000 )

  6  /

10000000 rows created.

Elapsed: 00:06:37.75

Now, disabled the trigger and used DEFAULT clause to INSERT 10 million records and also to populate the SALES_SEQ column in SALES table.

SQL> alter trigger SALES_SURROGATE_KEY disable;

Trigger altered.

SQL> alter table SALES modify SALE_SEQ default SEQ_SALES.nextval;

Table altered.

Again, inserting 10 million rows for testing the Insert time.

SQL> insert /*+ APPEND */ into SALES

  2  select rownum, ...

  3  from

  4   ( select 1 from dual connect by level <= 10000 ),

  5   ( select 1 from dual connect by level <= 1000 )

  6  /

10000000 rows created.

Elapsed: 00:00:04.73