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 
Leave a reply to connormcdonald Cancel reply