sequences

Quick tip–identity columns

connormcdonald , , ,

Lets say I’ve been reading about schema separation, and thus I am going to have a schema which owns all of my objects, which I’ll call APP_OWNER, which will have no connection privilege and a separate schema called APP_ADMIN which will take care of all of the DDL tasks.

Here’s my setup:


SQL> create user app_owner identified by app_owner;
 
User created.
 
SQL> grant
  2    create session,
  3    create any table,
  4    create any sequence,
  5  to app_admin identified by app_admin;
 
Grant succeeded.

I have granted APP_ADMIN the ability to create tables in any schema, and create sequences in any schema. I did the latter, because I know that if I use the “IDENTITY” clause for a column, then behind the scenes I’ll be creating a sequence to populate those ascending values.  So it looks like I am ready to go and create my objects.  Let’s create that first table


SQL> conn app_admin/app_admin
Connected.
 
SQL> create table app_owner.t(pk integer generated always as identity);
create table app_owner.t(pk integer generated always as identity)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Hmmm….that is not what we were expecting. It turns out that to create an identity column in another schema you need more than just CREATE ANY SEQUENCE. You also need SELECT ANY SEQUENCE.


SQL> conn / as sysdba
Connected. 
SQL> grant
  2    create session,
  3    create any table,
  4    create any sequence,
  5    select any sequence
  6  to app_admin identified by app_admin;
 
Grant succeeded.
 
SQL> conn app_admin/app_admin
Connected.
 
SQL> create table app_owner.t(pk integer generated always as identity);
 
Table created.

And there we go Smile

Footnote: If you’ve never seen the syntax “grant <privs> to <user> identified by <pass>” it is a quick shortcut to both create the user account and assign privileges in a single command

Let me START WITH sequences

connormcdonald ,

It’s always cool that you can learn stuff every single day, even on the most simple of topics.  This one came from an AskTom question.  We define a sequence to start with 100, and then alter it to modify the INCREMENT BY.



SQL> create sequence test_seq INCREMENT BY 25 START WITH 100 MAXVALUE 1000 NOCACHE NOCYCLE;

Sequence created.

SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     : 1
MAX_VALUE                     : 1000
INCREMENT_BY                  : 25
CYCLE_FLAG                    : N
ORDER_FLAG                    : N
CACHE_SIZE                    : 0
LAST_NUMBER                   : 100
PARTITION_COUNT               :
SESSION_FLAG                  : N
KEEP_VALUE                    : N
-----------------

PL/SQL procedure successfully completed.

SQL> alter sequence test_seq INCREMENT BY 1 MAXVALUE 999999 NOCACHE NOCYCLE;

Sequence altered.

SQL> select test_seq.nextval from dual;

So the question is – what will the NEXTVAL query above return ?

Well, the following might come as a surprise.



SQL> select test_seq.nextval from dual;

   NEXTVAL
----------
        76

Yes, even though the sequence was defined as START WITH 100, when we look at the sequence definition, the combination of START WITH 100, and INCREMENT BY 25 has the following impact on the LAST_NUMBER value stored, when the ALTER command was issued.


SQL> @pt "select * From user_sequences where sequence_name = 'TEST_SEQ'"
SEQUENCE_NAME                 : TEST_SEQ
MIN_VALUE                     : 1
MAX_VALUE                     : 999999
INCREMENT_BY                  : 1
CYCLE_FLAG                    : N
ORDER_FLAG                    : N
CACHE_SIZE                    : 0
LAST_NUMBER                   : 76
PARTITION_COUNT               :
SESSION_FLAG                  : N
KEEP_VALUE                    : N
-----------------

PL/SQL procedure successfully completed.

SQL>

This is alluded to in the documentation for ALTER SEQUENCE

“If you change the INCREMENT BY value before the first invocation of NEXTVAL, then some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value. “

Something new learned every day

connormcdonald , , ,

One of the reasons I leapt at the chance to be on the AskTom team when we resurrected the site, was that it’s like free training.  You get questions on topics you have not visited before, and you get new angles on things you thought you already knew.

Just today, someone posted a question about the new DEFAULT ON NULL syntax in 12c, with the following observation:

“Standard inserts advance the sequence only when needed, but a PL/SQL for-loop advances the sequence all the time”

And here was their test case… (Sidebar:  WOO HOO!!! Yippee !!!! A test case !! A test case !!! People get it !!! Smile )


SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> BEGIN
  2  INSERT INTO t1 (col1, description) VALUES (1000,'1000,DESCRIPTION');
  3  INSERT INTO t1 (col1, description) VALUES (1001,'1001,DESCRIPTION');
  4  INSERT INTO t1 (col1, description) VALUES (1002,'1002,DESCRIPTION');
  5  INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
      1000 1000,DESCRIPTION
      1001 1001,DESCRIPTION
      1002 1002,DESCRIPTION
         1 DESCRIPTION only

SQL>
SQL> DROP TABLE t1;

Table dropped.

SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> BEGIN
  2  FOR i IN 1..3 LOOP
  3  INSERT INTO t1 (col1, description) VALUES (999 + i,999 + i || ',DESCRIPTION');
  4  END LOOP;
  5
  6  INSERT INTO t1 (description) VALUES ('DESCRIPTION only');
  7
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
      1000 1000,DESCRIPTION
      1001 1001,DESCRIPTION
      1002 1002,DESCRIPTION
         4 DESCRIPTION only

That seems fairly conclusive – the sequence was fetched once in the first instance, and four times in the second instance.

But we can explore a little further.  I took out the PL/SQL elements and reduced it down to a simpler version


SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (1000,'1000,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (1001,'1001,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (1002,'1002,DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
      1000 1000,DESCRIPTION
      1001 1001,DESCRIPTION
      1002 1002,DESCRIPTION
         1 DESCRIPTION only

SQL>
SQL> DROP TABLE t1;

Table dropped.

SQL> DROP SEQUENCE default_seq;

Sequence dropped.

SQL> CREATE SEQUENCE default_seq;

Sequence created.

SQL>
SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT ON NULL default_seq.NEXTVAL NOT NULL ENABLE,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
       999 999,DESCRIPTION
       999 999,DESCRIPTION
       999 999,DESCRIPTION
         4 DESCRIPTION only

SQL>
SQL>
SQL>
SQL>

No PL/SQL here yet the observation is the same.  In fact, the only difference is that in the latter case, the INSERT’s contain an expression for column bound the sequence.  And that’s the clue.

The column is defined as:

DEFAULT ON NULL default_seq.NEXTVAL

So only if the target value for the column is null, we’ll use the sequence.  For an expression, we won’t know whether we’ll need a sequence value until the moment that expression is evaluated.  So we might need a sequence value handy to slot in there in case the expression comes out as null.  So my hypothesis is that we’ll need to grab one and bring it along for the ride, so to speak.  Let’s redo the experiment, still with a default but without the ON NULL.




SQL> CREATE TABLE t1 (
  2  col1 NUMBER DEFAULT default_seq.NEXTVAL,
  3  description VARCHAR2(30)
  4  );

Table created.

SQL>
SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (999 + :i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
       999 999,DESCRIPTION
       999 999,DESCRIPTION
       999 999,DESCRIPTION
         1 DESCRIPTION only

So there you have it. Expressions combined with DEFAULT ON NULL might massage your sequence more than you expect. Not this this should be a problem, because we don’t really care what number the sequence is anyway. But you might want to bump up your sequence cache size to accommodate it. Note, that the same applies for just a simple bind variable (I’ve put the ON NULL clause back in and run the script below)


SQL> variable i number
SQL> exec :i := 0;

PL/SQL procedure successfully completed.

SQL>
SQL> INSERT INTO t1 (col1, description) VALUES (:i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (:i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (col1, description) VALUES (:i,999  || ',DESCRIPTION');

1 row created.

SQL> INSERT INTO t1 (description) VALUES ('DESCRIPTION only');

1 row created.

SQL>
SQL> SELECT * FROM t1;

      COL1 DESCRIPTION
---------- ------------------------------
         0 999,DESCRIPTION
         0 999,DESCRIPTION
         0 999,DESCRIPTION
         4 DESCRIPTION only

DEFAULT SEQ.NEXTVAL in 12c

connormcdonald , ,

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 Smile

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 Smile

And so there you have it.  The new DEFAULT additions in 12c are a very nice touch indeed.

You dont need that sequence number

connormcdonald ,

I’ve lost track of the number of times I see this sequence (no pun intended) of actions in code:


SELECT my_sequence.nextval INTO :some_variable FROM dual;
 
INSERT INTO my_table VALUES (:some_variable, ....);

My question is always “Why?” What was it that made you so desperate in need of that sequence value that you needed it before you inserted it ? It is simply, easier, and more efficient just to get it back from the insert statement itself.


INSERT INTO MY_TABLE (seq_col, ...)
VALUES (my_sequence.nextval, ...)
RETURNING seq_col INTO :some_variable;

And with 12c, sequences can now be nominated as part of the DEFAULT value for a column, so you don’t need to refer to it at all…and you STILL can have the number


INSERT INTO MY_TABLE ( )
VALUES ( ...)
RETURNING seq_col INTO :some_variable;

So say it with pride … “I DONT WANT TO KNOW WHAT MY SEQUENCE VALUES ARE!!!” 🙂