Reference Sequences Directly in PS/SQL Expressions in Oracle 11g

With 11g, you can now reference sequences directly in PL/SQL expressions without the archaic “select from dual” construct. The result is more streamlined code that is easier to read and maintain. Oracle also promises improved performance and scalability. Let’s start with a PL/SQL block written the “old fashioned” way with a “select from dual” construct being used to pull values from a sequence:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This block executes in 61.47 seconds (averaged across three executions). With 11g, you can streamline the code by referencing the NEXTVAL and CURRVAL pseudocolumns directly within the PL/SQL code, as shown here:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The average execution time with this block is 61.90 seconds. While the ability to reference the sequence value directly in PL/SQL has simplified the code, a performance gain has not been realized. In fact, the performance has degraded slightly. To investigate further, the previous tests are repeated with sequences using various cache sizes. The results are summarized here.

Time (in Seconds) to Fetch 100,000 Values from a Sequence

 

 

 

 

So it would appear that the inline construct has a slight performance advantage, particularly when the sequences are defined with a cache. However, the advantage is so slight that I am reluctant to say definitively that the new construct is indeed faster. A second round of testing with the same parameters as before and the performance advantage now tips in favor of the “select from dual” construct.

 

Time (in Seconds) to Fetch 100,000 Values from a Sequence

 

 

 

 

As of this writing, the ability to reference a sequence generator directly in a PL/SQL expression can definitely streamline your code, but no performance benefits are realized at this time.