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 184.108.40.206.0
Oracle SQL Developer 220.127.116.11.
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; ...
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.
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.