The new row_limiting_clause allows us to perform Top-N queries without having to use analytic functions or nested inline views. We can define rows to be skipped (offset), how to handle ties, percent and absolute number of rows to be returned – just about everything we need.

There is one thing we have to take care by ourselves … the data should be ordered to get a decent result.

Fetching first 10 rows of hr.employees table ordered by salary

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH FIRST 10 ROWS ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Lex                  De Haan                        17000
Neena                Kochhar                        17000
John                 Russell                        14000
Karen                Partners                       13500
Michael              Hartstein                      13000
Nancy                Greenberg                      12000
Shelley              Higgins                        12000 
Alberto              Errazuriz                      12000
Lisa                 Ozer                           11500

10 rows selected.

Skip first 5 rows and fetch next 2 rows.

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 2 ROWS ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Michael              Hartstein                      13000
Nancy                Greenberg                      12000

Handling Ties

When we take a look at the result of the first query, we can see, that besides Nancy Greenberg two more rows exist with exactly the same salary (ties) – to get them by the second query we have to tell ORACLE how to handle ties.

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 2 ROWS WITH TIES;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Michael              Hartstein                      13000
Nancy                Greenberg                      12000
Shelley              Higgins                        12000
Alberto              Errazuriz                      12000

Per Cent top-N Queries

A top-N query per cent is also possible. The number of rows to be returned is always a ceiled value (eg. 5% of 107 rows equals 5.35 which will be ceiled to 6 rows).

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH FIRST 5 PERCENT ROWS ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
John                 Russell                        14000
Karen                Partners                       13500
Michael              Hartstein                      13000

6 rows selected.

ORACLE does not care whether you write ROWS or ROW. Furthermore FIRST and NEXT do not have a semantic meaning to the SQL statement and are used for readability issues.

SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH NEXT 5 PERCENT ROW ONLY;

FIRST_NAME           LAST_NAME                 SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
John                 Russell                        14000
Karen                Partners                       13500
Michael              Hartstein                      13000

6 rows selected.

Limitations

Two things you cannot do:

• per cent offset
• offset with ties

Performance

The execution plans for the native top-n queries have fewer steps than the implementations using analytic functions or nested inline views.

Looking at the filters in the execution plans, we will find some “analytic-function-like” implementations.

EXPLAIN PLAN FOR SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
 FETCH FIRST 5 PERCENT ROWS ONLY;

Explained.

SELECT *
  FROM TABLE(SYS.DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 720055818
---------------------------------------------------------------------------------
| Id | Operation          | Name        | Rows  | Bytes | Cost (%CPU)|     Time |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |  107  |  8346 |       3 (0)| 00:00:01 |
|* 1 | VIEW               |             |  107  |  8346 |       3 (0)| 00:00:01 |
|  2 | WINDOW SORT        |             |  107  |  2033 |       3 (0)| 00:00:01 |
|  3 | TABLE ACCESS FULL  | EMPLOYEES   |  107  |  2033 |       3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_
 subquery$_002"."rowlimit_$$_total"*5/100))

16 rows selected.

EXPLAIN PLAN FOR 
SELECT first_name, last_name, salary
  FROM hr.employees
 ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 5 PERCENT ROWS WITH TIES;

Explained.

SELECT *
  FROM TABLE(SYS.DBMS_XPLAN.DISPLAY()); 

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 720055818
---------------------------------------------------------------------------------
| Id | Operation           | Name        | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |             |  107 |  9737 |       3 (0)| 00:00:01 |
|* 1 | VIEW                |             |  107 |  9737 |       3 (0)| 00:00:01 |
|  2 | WINDOW SORT         |             |  107 |  2033 |       3 (0)| 00:00:01 |
|  3 | TABLE ACCESS FULL   | EMPLOYEES   |  107 |  2033 |       3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber">5 AND
 "from$_subquery$_002"."rowlimit_$$_rank"<=CASE WHEN (5>=0) THEN 5 ELSE
 0 END +CEIL("from$_subquery$_002"."rowlimit_$$_total"*5/100))

17 rows selected. 

Conclusion

In my opinion native top-n query is a very handy feature helping us to write code that is more maintainable and readable. It should be used as soon as we are on ORACLE 12c.