TOP N and Pagination in Oracle 12c

Other than Oracle, most of OLTP Databases already had set of SQL syntax for fetching Top N.

In Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses—together called the row limiting clause.

Syntax for the same

TOP_N_Syntax
TOP N SQL SYNTAX

Oracle incorporate Magical analytical functions to imposed Ordering and Top N Fetch at same place as ease for coding SQL.

Let’s Create sample dataset to understand it better.

CREATE TABLE TST_TOPN AS
(SELECT ROWNUM RN, 'AAAAAAAA' COL2 FROM DUAL CONNECT BY ROWNUM<10000);
Fetch Top 10 records based on some column ordering defined.
SELECT RN, COL2
FROM TST_TOPN
ORDER BY RN DESC
FETCH FIRST 10 ROWS ONLY; -- Fetch Top 10 Records as per RN Order by Descending.
 
As per new Syntax, we can specify order by and fetching logic at 
same level.

Output:
 
RN COL2
---- --------
9999 AAAAAAAA
9998 AAAAAAAA
9997 AAAAAAAA
9996 AAAAAAAA
9995 AAAAAAAA
9994 AAAAAAAA
9993 AAAAAAAA
9992 AAAAAAAA
9991 AAAAAAAA
9990 AAAAAAAA
 
-------------------------------

Lets check underlying plan, on Oracle internal implementations.
 
SQL PLAN:
 
Plan hash value: 1139778702
 
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 28 | | | |
|* 1 | VIEW | | 1 | 9999 | 10 |00:00:00.01 | 28 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 9999 | 10 |00:00:00.01 | 28 | 6144 | 6144 | 6144 (0)|
| 3 | TABLE ACCESS FULL | TST_TOPN | 1 | 9999 | 9999 |00:00:00.01 | 28 | | | |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("RN") DESC )<=10)

As per filter Conditions captured, oracle internally transform SQL using Analytical function and used ROW_NUMBER() Analytical condition to served Top N Conditions.
“ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION(“RN”) DESC )”

Note :
Cardinality estimations (as highlighted) on expected rows are far offset(9999) with that compared to actual rows output expected(10).

Lets understand OFFSET Clause:
To paginate through a result set and to get N rows at a time from a specific page in the result set we can use OFFSET clause.

SELECT RN, COL2
FROM TST_TOPN
ORDER BY RN DESC
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
/* Skip first 5 rows and print next 10 rows as per ordering defined*/
Output:
RN COL2
---- --------
9994 AAAAAAAA
9993 AAAAAAAA
9992 AAAAAAAA
9991 AAAAAAAA
9990 AAAAAAAA
9989 AAAAAAAA
9988 AAAAAAAA
9987 AAAAAAAA
9986 AAAAAAAA
9985 AAAAAAAA
 
Plan:
 
Plan hash value: 1139778702
 
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 28 | | | |
|* 1 | VIEW | | 1 | 9999 | 10 |00:00:00.01 | 28 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 9999 | 15 |00:00:00.01 | 28 | 6144 | 6144 | 6144 (0)|
| 3 | TABLE ACCESS FULL | TST_TOPN | 1 | 9999 | 9999 |00:00:00.01 | 28 | | | |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN (5>=0) THEN 5 ELSE 0 END +10 AND
 "from$_subquery$_002"."rowlimit_$$_rownumber">5))
 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("RN") DESC )<=CASE WHEN (5>=0) THEN 5 ELSE 0 END
 +10)

As you can see, under the covers, it is rewriting the query to use inline views, CASE and analytics automating something that was previously non nutritive and complex.

Fetch first 1 percent of records from data set in a specific ordered.

Instead of fetch on basis of set of specific records, we can fetch records on basis of some percentage.

SELECT COL2
FROM TST_TOPN
WHERE RN IS NOT NULL
ORDER BY RN DESC
FETCH FIRST 1 PERCENT ROWS ONLY;
Plan hash value: 261699775
 
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | VIEW | | 9999 | | | |
| 2 | WINDOW SORT | | 9999 | 478K| 448K| 424K (0)|
|* 3 | TABLE ACCESS FULL| TST_TOPN | 9999 | | | |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$
 _subquery$_002"."rowlimit_$$_total"*1/100))
 3 - filter("RN" IS NOT NULL)
 
Oracle automatically added below filtered conditions, to fetch first 1 percent of records.
"from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*1/100

 

Note:
Whenever Rows are fetched based on percentage, oracle need to fetch complete set of data.After fetching complete set it’s apply filtered conditions.
Personally believe as a Developer, whenever possible specify exact number of records to be fetched, instead of specifying percentage.


With Ties Options

Suppose a scenario in which along with TOP N records, we need to display rows with same ordered columns-values as of Nth Records.
Oracle provide an option to fetch N+(Duplicate) records in case matching records exist for last row.
Let’s create a sample data set to understand it better.

 
CREATE TABLE TST_TOPN1 AS
(SELECT ROWNUM RN,MOD(ROWNUM,5) COL2 FROM DUAL CONNECT BY ROWNUM<50);
 
SELECT *
FROM TST_TOPN1
ORDER BY COL2 DESC
FETCH FIRST 5 ROWS WITH TIES; 
-- fetch fist 5 rows , along with ties record for ordered column.
 
OUTPUT: 
 
 RN COL2
---------- ----------
 4 4
 49 4
 44 4
 39 4
 34 4
 29 4
 24 4
 19 4
 14 4
 9 4
 
10 rows selected.

Same SQL with ONLY Option
 
SELECT *
FROM TST_TOPN1
ORDER BY COL2 DESC
FETCH FIRST 5 ROWS ONLY
 
 RN COL2
---------- ----------
 4 4
 24 4
 19 4
 14 4
 9 4
 
5 rows selected.

Please note:
Bind values can be used in place of Top N literals.
SELECT * FROM TST_TOPN1 ORDER BY COL2 DESC FETCH FIRST :1 ROWS ONLY;

 

For further insight, please check.
oracle-12c-top-n-pagination-performance-considerations

Any Feedback or pointers would be highly appreciated.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features and tagged , , , . Bookmark the permalink.

3 Responses to TOP N and Pagination in Oracle 12c

  1. Pingback: Oracle 12c TOP N Pagination performance considerations. | Oracle Insight and Performance concepts

  2. Pratik says:

    crystal clear, thanks for brief explanation..

    Like

  3. Siddharth Samant says:

    Very nice and informative article Deepak…Thanks for sharing this

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s