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. 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. 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 :-)

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>