In versions of the Oracle Database prior to 11g Release 1, to populate a variable with the next value from a sequence you would have call the NEXTVAL function from within a SQL statement. From Oracle Database 11g Release 1 you can now use the NEXTVAL function in a PL/SQL expression.

So other than less typing, does using a sequence in this way improve performance?

The following examples are all based on the following sequence

CREATE SEQUENCE emp_seq
/

and were developed using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Oracle SQL Developer 3.2.20.10.

To recap, the pre 11g method was:

...
SELECT emp_seq.nextval
  INTO some_variable
  FROM dual;
...

and from 11g Release one you can use the following:

...
some_variable := emp_seq.nextval;
...

Better Performance?

Using the method described by Adrian Billington you will be able see if the 11g method improves performance.

ALTER SESSION SET SQL_TRACE = TRUE
/

DECLARE

   emp_id PLS_INTEGER;

BEGIN

   FOR i IN 1 .. 10000
   LOOP

      emp_id := emp_seq.NEXTVAL;   

   END LOOP;

   FOR i IN 1 .. 10000
   LOOP

      SELECT emp_seq.NEXTVAL AS nval
        INTO emp_id
        FROM dual;

   END LOOP;

END;
/

SQL Trace is enabled and the new 11g method of using NEXTVAL via an expression is called 10000 times after which the pre 11g method of using a SQL statement is also called 10000 times. Note the alias of nval is used with the SQL statement method this will help us identify the SQL statement method.

Here is the output from the trace file. The first entry is for the new 11g method and the second entry is for pre 11g method (as proved by the use of the alias nval)

The trace file shows very little differences when using the new 11g method. Which is not surprising as the trace file also shows that “under the covers” Oracle has not changed the implementation for the new 11g method and NEXTVAL is still obtained via a SQL statement.

Summary

In this article I have shown the two methods that are available to populate a variable with the next value from a sequence and that there is no performance gains to be had by choosing one method over another.

Acknowledgements

Adrian Billington (oracle-developer.net)