Using DEFAULT clause for populating Surrogate Keys
In Oracle Database 12 c , 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 t