What is ROWNUM=1 ?

Is rownum=1 the first row returned? well, it depends :

select rownum, ename
from emp
order by ename;
ROWNUM ENAME
---------- ----------
11 ADAMS
2 ALLEN
6 BLAKE
7 CLARK
13 FORD
12 JAMES
4 JONES
9 KING
5 MARTIN
14 MILLER
8 SCOTT
1 SMITH
10 TURNER
3 WARD

The ROWNUM is evaluated before the order by

select rownum
from emp
group by rownum
having rownum between 3 and 6;
ROWNUM
----------
6
5
3
4

the rownum is selected before the GROUP BY and before the HAVING.

select
rownum,
sal,
median(sal) over ()
from emp;
ROWNUM SAL MEDIAN(SAL)OVER()
---------- ---------- -----------------
1 800 1550
12 950 1550
11 1100 1550
3 1250 1550
5 1250 1550
14 1300 1550
10 1500 1550
2 1600 1550
7 2450 1550
6 2850 1550
4 2975 1550
8 3000 1550
13 3000 1550
9 5000 1550

The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function

2 thoughts on “What is ROWNUM=1 ?”

  1. Brian Ballsun-Stanton

    Interesting. Which version is this? Also, could you post the source of your sample data? This would make an excellent exercise for the performance and tuning class I’m helping with.

  2. Laurent Schneider Post author

    the source is the default scott schema that can be installed with @?/rdbms/admin/utlsampl


    CREATE TABLE DEPT
    (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) ) ;
    CREATE TABLE EMP
    (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
    INSERT INTO DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES
    (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES
    (40,'OPERATIONS','BOSTON');
    INSERT INTO EMP VALUES
    (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
    INSERT INTO EMP VALUES
    (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
    INSERT INTO EMP VALUES
    (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
    INSERT INTO EMP VALUES
    (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
    INSERT INTO EMP VALUES
    (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);INSERT INTO EMP VALUES
    (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
    INSERT INTO EMP VALUES
    (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
    INSERT INTO EMP VALUES
    (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
    INSERT INTO EMP VALUES
    (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);INSERT INTO EMP VALUES
    (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
    INSERT INTO EMP VALUES
    (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
    INSERT INTO EMP VALUES
    (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
    INSERT INTO EMP VALUES
    (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
    INSERT INTO EMP VALUES
    (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

    I tried with 9iR2, 10gR1, 10gR2 and 11gR1. Median does not exist in 9iR2 but PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY SAL) OVER () does provide the same result. The group by with unsorted rows is using a special optimizer access plan which is HASH GROUP BY and that appeared in 10gR2

    Do not hesitate to come back with more questions 🙂

Comments are closed.