Something new learned every day

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


Advertisements

One thought on “Something new learned every day

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s