1. Using RANK function, with a faster execution plan (WINDOW SORT PUSHED RANK)
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
   FROM
   (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
   RANK() OVER
   (ORDER BY SAL Desc) AS Emp_Rank
   FROM Emp)
   WHERE Emp_Rank <= 5;
2. Using row_number function (speed up top-n queries)
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
   FROM
   (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal,
   row_number() OVER
   (ORDER BY SAL Desc) Emp_Rank
   FROM Emp)
   WHERE Emp_Rank <= 5;
3. Using ROWNUM
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
   FROM
   (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
   FROM Emp
   ORDER BY SAL Desc)
   WHERE ROWNUM <= 5;

Using ROWNUM to materialize an in-line view is not efficient, and it’s better to materialize the subquery using the SQL-99 WITH clause.  ROWNUM can cause performance problems.  Using ROWNUM may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans.  One solution is to always include an all_rows hint when using ROWNUM to perform a top-n query.  See tuning SQL with “rownum”.

Share on Facebook