Monthly Archives: May 2014

In Oracle SQL, why doesn’t ROWNUM = 2 work?

The inspiration for this post came after recently overhearing a developer asking why his query was not returning a row. It had been working fine, the only change was instead of ROWNUM = 1, he had changed it to ROWNUM = 2.

The query was could have been doing something similar to the following:

SELECT x.*
  FROM (SELECT e.employee_id,
               e.last_name,
               e.salary
          FROM employees e
         ORDER BY salary DESC) x
 WHERE ROWNUM = 1 
/

which returns information about the top earning employee.

However when the last line of the query is changed to ROWNUM = 2 to find the second highest earning employee no rows are returned and this is what was causing confusion.

I have read enough Tom Kyte to understand superficially why the change stopped the query from working but not in enough detail to stand up in front of my peers or even my cat for that matter and confidently explain why. This post is my attempt to change that.

What is ROWNUM? In Oracle terms, ROWNUM is a pseudocolumn which returns a number for each row returned by a query. The ROWNUM returned is not permanently assigned to a row. A ROWNUM value is assigned after it passes the predicate part of the query but before any sorting or aggregation. It is important to know that the ROWNUM is only incremented after it has been assigned.

Stepping through our example, where ROWNUM = 2. The first row fetched is assigned a ROWNUM of 1 and makes this condition false (So at this point ROWNUM = 1, we are looking for ROWNUM = 2 or to put it another way 1 = 2 which of course is false), the second row to be fetched is now the first row and ROWNUM is still 1 (It hasn’t incremented because it wasn’t assigned) so again the condition is false and this repeats for all rows so none are returned.

So how do you find the second highest earning employee? That will be the subject of a future post.

Acknowledgements

On ROWNUM and Limiting Results By Tom Kyte

Does using a sequence in a PL/SQL expression improve performance?

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)

20140502_TraceFile

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)