Tags
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.