• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: Top-N

ORACLE 12c Release 1 – Native Top-N Queries

03 Tuesday Sep 2013

Posted by troroblog in Uncategorized

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 1, SQL, Top-N

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.

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...