Posted by One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column.
Of course, some may say “big deal” – after all, it is trivial to create a simple trigger to do the job. And perhaps, we “crafty” folks at Oracle are just doing a hidden trigger in the background anyway 
Well… we’re not. We’ve put some work into this to make it sing. Let’s look at a demo. First, here’ s the trigger-based approached.
SQL> create sequence SEQ cache 1000;
Sequence created.
SQL>
SQL> create table T ( x int , y int);
Table created.
SQL>
SQL> create or replace
2 trigger TRG before insert on T
3 for each row
4 begin
5 :new.x := seq.nextval;
6 end;
7 /
Trigger created.
SQL>
SQL> drop table logger purge;
Table dropped.
SQL>
SQL> create table logger ( sid int, started timestamp, stopped timestamp);
Table created.
SQL>
SQL> create or replace procedure hammer is
2 begin
3 insert into logger values ( sys_context('USERENV','SID'), systimestamp , null );
4 for i in 1 .. 100000 loop
5 insert into T (y) values (i);
6 commit;
7 end loop;
8 update logger set stopped = systimestamp where sid = sys_context('USERENV','SID');
9 commit;
10 end;
11 /
Procedure created.
SQL>
SQL> declare
2 j int;
3 begin
4 for i in 1 .. 4 loop
5 dbms_job.submit(j,'hammer;');
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
We have a table T with a standard trigger-based sequence number assignment. We are going to hammer that table (hence the procedure name) with 4 concurrent sessions, each doing 100,000 inserts into the table to mimic a high utilisation transaction table. We can see what throughput we achieved once the jobs are done.
SQL> select 4*100000 / avg(extract(second from (stopped-started))) rows_per_sec
2 from logger;
ROWS_PER_SEC
------------
19965.3103
Now let’s be clear. Just under 20,000 inserts per second (on my laptop) is nothing to sneeze at. The trigger certainly is not “mauling” the performance, but the question is – would it be better using the new native default feature in 12c. Let’s take a look
SQL> drop table T purge;
Table dropped.
SQL>
SQL> drop sequence SEQ;
Sequence dropped.
SQL> create sequence SEQ cache 1000;
Sequence created.
SQL>
SQL> create table T ( x int default seq.nextval, y int);
Table created.
SQL>
SQL> drop table logger purge;
Table dropped.
SQL>
SQL> create table logger ( sid int, started timestamp, stopped timestamp);
Table created.
SQL>
SQL> create or replace procedure hammer is
2 begin
3 insert into logger values ( sys_context('USERENV','SID'), systimestamp , null );
4 for i in 1 .. 100000 loop
5 insert into T (y) values (i);
6 commit;
7 end loop;
8 update logger set stopped = systimestamp where sid = sys_context('USERENV','SID');
9 commit;
10 end;
11 /
Procedure created.
SQL>
SQL> declare
2 j int;
3 begin
4 for i in 1 .. 4 loop
5 dbms_job.submit(j,'hammer;');
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL> select 4*100000 / avg(extract(second from (stopped-started))) rows_per_sec from logger;
ROWS_PER_SEC
------------
27709.1439
1 row selected.
Man… that is smoking hot performance
And so there you have it. The new DEFAULT additions in 12c are a very nice touch indeed.
Learning is not a spectator sport 
Just to be sure I got it… the “classic” approach with a trigger did 20K rows/sec. And the 12c approach with “default Seq.Nextval” did 27K rows/sec. That’s a speed-up of ~30%. That’s very nice. But, as performance improvements go, it isn’t earth-shattering.
However… the table has no PK constraint on the column, “x int”, that models the surrogate PK. You must have the index present to get fair time comparisons.
It would be nice to know the baseline speed (you just get lucky and insert values that don’t violate the PK constraint). You can arrange that easily with present harness just my making the four sessions start with values that are fare enough apart.
Only with these timings will we be able to say something useful.
Why do you favour the “causal” approach rather than the formal “identity” construct?
Why do you give credence to the notion that the value of a surrogate key should be settable manually. This seems to me to be a suspect notion. I don’t buy the use case you invented to start your discussion: accidental delete and the wish to reinstate the deleted row with the same PK by manual insert. That’s almost like wanting it to get the same RowID that it originally had.
Now I wonder how the case of “insert into T(x,y) values(seq.nextval,i);” compares to both cases you have demonstrated.
Now we’ve recently migrated to 12c, we can revisit our triggers!
Bryn’s comments provide an interesting perspective, thank you.
Oops, I forgot to comment on Connor’s use of the evil slow-by-slow “for loop around single row insert” locution in his test harness. The time for the notorious SQL-PL/SQL back-and-fore might be swamping the effect that the experiment aims to time. Insert-select from source tables with pre-populated, unique, values for the target PK col? Please repeat!
This was not a “how much load can I crank through” test, otherwise insert-select would have been used. This was a test harness to (approximately) match where such sequences are most commonly used – lots of single row inserts into a transaction-style table, hence the: “to mimic a high utilisation transaction table” comment in the blog description. It’s the same reason there’s a commit for each insert, albeit the plsql-optimized version
When I write that trigger (for 11g, for instance) I usually say:
IF :new.x IS NULL THEN
:new.x := seq.nextval;
END IF;
That makes sure that if I WANT to assign a specific PK – say for replacing accidentally deleted rows, I can. I presume that since the 12c version uses the DEFAULT clause, that has the same behavior, right?
That is correct.
Thanks for stopping by.