Exploring SQL Features in Oracle 12c / 19c

Introduction

Oracle 12c introduced a set of modern SQL constructs that dramatically simplify row limiting and pagination. Prior to this, developers relied on cumbersome ROWNUM tricks that were difficult to read and maintain. Oracle 19c, the long-term support release, fully supports these features  making them the recommended approach for all new development.

In this article, we will explore these features and illustrate them with real-world examples.

Schema	SCOTT
Table	EMP

 

Features covered in this article:

•        FETCH FIRST — Limiting rows returned

•        Pagination Using OFFSET and FETCH

•        FETCH WITH TIES — Including tied values

 1. Limiting Rows Using FETCH FIRST

The FETCH FIRST clause retrieves a limited number of rows from a query result.

Example -1: Retrieve first 3 employees

SELECT empno, ename, job, sal
FROM emp
ORDER BY empno
FETCH FIRST 3 ROWS ONLY;

  

Explanation:

The query returns the first three rows from the EMP table ordered by employee number without using ROWNUM or no subquery.

Comparison: ROWNUM Approach

Prior to Oracle 12c, the same result required a nested subquery:

Example using ROWNUM 

SELECT empno, ename, sal

FROM (

     SELECT empno, ename, sal

     FROM emp

     ORDER BY sal DESC

)

WHERE ROWNUM <= 3;

Analyzing Performance of both query:

EXPLAIN PLAN a primary tool for analyzing query performance

Performance: Both approaches perform similarly at the execution level. However, FETCH FIRST provides significantly better readability, maintainability, and is the recommended modern approach

Example -2: Top 3 highest salaries

SELECT empno, ename, sal

FROM emp

ORDER BY sal DESC

FETCH FIRST 3 ROWS ONLY;

 Explanation:

Fetch all employees by salary in descending order and returns only the top three. This is much better and clean as compare to ROWNUM.

2. Fetching Top N Records Using ORDER BY

FETCH FIRST can be combined with ORDER BY on any column — not just salary. This makes it easy to retrieve the earliest hired employees, the most recently created records, or any other ranked subset.

Example: Top 3 earliest hired employees

Retrieve the three employees with the longest tenure

 

SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate ASC
FETCH FIRST 3 ROWS ONLY;

Explanation: This query sorts employees by hire date in ascending order and returns the three longest serving employees.

 3. Pagination Using OFFSET and FETCH

Pagination is essential in web applications and reporting tools such as Oracle APEX.

Example: Skip first 2 employees and display next 5

SELECT empno, ename, sal
FROM emp
ORDER BY empno
OFFSET 2 ROWS
FETCH NEXT 5 ROWS ONLY;

 
Explanation:

Clause	Behaviour
OFFSET 2 ROWS	Skips the first 2 rows
FETCH NEXT 5 ROWS ONLY	Returns the next 5 rows after the offset

This is good in application reporting and dashboard queries.

 4. Using FETCH WITH TIES

Sometimes multiple rows share the same ranking value. The WITH TIES modifier ensures that all rows tied on the last qualifying value are included in the result

Example: Top 3 salaries including ties

SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC
FETCH FIRST 3 ROWS WITH TIES;

Explanation: If the third and fourth employees have the same salary, both rows will be returned.

Conclusion

The FETCH FIRST, OFFSET/FETCH, and WITH TIES clauses introduced in Oracle 12c and fully supported in Oracle 19c provide a clean, readable, and ANSI-compliant alternative to  ROWNUM techniques.

 

•        FETCH FIRST replaces nested ROWNUM subqueries with a single readable clause

•        OFFSET and FETCH together enable robust, page-by-page result navigation

•        WITH TIES prevents incomplete results when rows share a boundary value

•        All three features work seamlessly with Oracle APEX and modern reporting tools