The Oracle FETCH FIRST story: from ROW_NUMBER() in 12c back to ROWNUM in 23ai
The FETCH FIRST ... ROWS ONLY clause arrived in the SQL standard with SQL:2008, and Oracle Database implemented it in 12cR1, released in June 2013.
Before that, there were two common ways to write a Top-N query, both requiring a subquery:
- Order the rows first, then apply
ROWNUMoutside:
SELECT *
FROM (
SELECT ...
FROM ...
ORDER BY ...
)
WHERE ROWNUM <= 42;
- Calculate an analytic
ROW_NUMBER(), then filter its result outside:
SELECT *
FROM (
SELECT ...,
ROW_NUMBER() OVER (ORDER BY ...) AS rn
FROM ...
)
WHERE rn <= 42;
The first subquery is necessary because the ordering must happen before the ROWNUM filter. The second is necessary because the analytic function cannot be evaluated in the same query block’s WHERE clause.
With FETCH FIRST, we can express the intention directly:
SELECT ...
FROM ...
ORDER BY ...
FETCH FIRST 42 ROWS ONLY;
However, a simpler SQL statement does not necessarily mean a simpler implementation.
First, a transformation to ROW_NUMBER
Like many additions to Oracle’s SQL syntax, FETCH FIRST was implemented through a transformation to existing constructs. Oracle initially chose the analytic ROW_NUMBER() solution.
This was the familiar rewrite in 12c, 18c, 19c, 21c, and early 23c releases. In the recent 23ai and 26ai releases tested here, the simple FETCH FIRST n ROWS ONLY case is rewritten using ROWNUM.
“AI” is not responsible for the change. Oracle 18c and 19c belong to the 12cR2 release family, with names reflecting the release year. Similarly, 26ai is still in the 23 release family, and “ai” replaced “c” with the 23.4 release.
Beyond the marketing names, the relevant change is fix control 35915968:
SQL> SELECT bugno, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 35915968;
BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ----------------------------------------------------- ------------------------
35915968 fetch first transformation using rownum 23.1.0
The OPTIMIZER_FEATURE_ENABLE column tells us the optimizer compatibility setting associated with the control.
There is another clue in ORACLE_HOME. In the 26ai installation used for this investigation, rdbms/admin/bundlefcp_DBBP.xml lists this control under the 23.4.0.0.0 bundle:
<bug id="35915968">
<fix_control default_value="1">35915968</fix_control>
</bug>
This is more useful than simply saying “23ai changed it.”
The first problem was costing
Choosing ROW_NUMBER() rather than ROWNUM already had a consequence in 12cR1: the optimizer did not apply the same first-k-row optimization.
I blogged about this in 2014: ROWNUM vs ROW_NUMBER() and 12c fetch first.
With ROWNUM <= 10, Oracle knew that it needed only the first ten rows and could cost an ordered index access accordingly. With the analytic rewrite, it could cost the access as if many more rows were needed, making a full scan and sort look preferable.
My recommendation was to add FIRST_ROWS(n), not the old FIRST_ROWS hint.
This was improved in 19c:
SQL> SELECT bugno, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 22174392;
BUGNO DESCRIPTION OPTIMIZER_FEATURE_ENABLE
---------- ---------------------------------------------------------------- ------------------------
22174392 first k row optimization for window function rownum predicate 19.1.0
I blogged about it in 2020: 19c: scalable Top-N queries without further hints to the query planner.
The improvement is visible in the execution plan as WINDOW NOSORT STOPKEY, the stopkey replacing the simple analytic filter WINDOW SORT PUSHED RANK, so that Oracle could cost the analytic Top-N query with the first-k-row objective and choose that access without the additional hint.
When an index supplies the required order, Oracle can stop early. Without an ordered access path, a sort may still be necessary.
Now, back to ROWNUM
The first-k-row is not the only difference between ROWNUM and the analytic function. ROWNUM was also acts as a non-mergeable view barrier, forcing the optimizer to treat the query block as an isolated inline view.
For the simple row limit tested here, Oracle 26ai now goes back to the other implementation:
SQL> EXPLAIN PLAN FOR
SELECT *
FROM dual
ORDER BY dummy
FETCH FIRST 42 ROWS ONLY;
SQL> SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PREDICATE'));
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
| 3 | TABLE ACCESS FULL| DUAL |
------------------------------------
Predicate Information:
----------------------
1 - filter(ROWNUM<=42)
The transformation is visible with DBMS_UTILITY.EXPAND_SQL_TEXT:
WITH
FUNCTION expand_my_sql(p_sql IN VARCHAR2) RETURN CLOB IS
v_output CLOB;
BEGIN
DBMS_UTILITY.EXPAND_SQL_TEXT(
input_sql_text => p_sql,
output_sql_text => v_output
);
RETURN v_output;
END;
SELECT expand_my_sql(
'SELECT * FROM DUAL ORDER BY DUMMY FETCH FIRST 42 ROWS ONLY'
) AS expanded_sql
/
Formatted for readability, the output is:
SELECT "A1"."DUMMY" "DUMMY"
FROM (
SELECT "A2"."DUMMY" "DUMMY"
FROM "SYS"."DUAL" "A2"
ORDER BY "A2"."DUMMY"
) "A1"
WHERE ROWNUM <= 42
We can restore the earlier transformation on the same database by disabling the change:
ALTER SESSION SET "_fix_control" = '35915968:0';
The expanded SQL becomes:
SELECT "A1"."DUMMY" "DUMMY"
FROM (
SELECT "A2"."DUMMY" "DUMMY",
"A2"."DUMMY" "rowlimit_$_0",
ROW_NUMBER() OVER (
ORDER BY "A2"."DUMMY"
) "rowlimit_$$_rownumber"
FROM "SYS"."DUAL" "A2"
) "A1"
WHERE "A1"."rowlimit_$$_rownumber" <= 42
ORDER BY "A1"."rowlimit_$_0"
And the plan uses the analytic operation again:
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | VIEW | |
|* 2 | WINDOW NOSORT STOPKEY| |
| 3 | TABLE ACCESS FULL | DUAL |
---------------------------------------
Predicate Information:
----------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=42)
2 - filter(ROW_NUMBER() OVER (ORDER BY "DUAL"."DUMMY")<=42)
More than a decade after introducing FETCH FIRST, Oracle is using the legacy ROWNUM solution for this case. It benefits from the existing first-k-row optimization and gives the optimizer a well-established row-limit boundary.
This is not a claim that all row-limiting clauses now use ROWNUM. OFFSET, percentages, and WITH TIES have additional semantics. In the same 26ai binary, another control, 35969400, can also select a native ROW LIMIT operation.
But for the case discussed here, going back to ROWNUM also avoids a wrong-results bug.
EXISTS and NOT EXISTS returning a row
A Stack Overflow question showed Oracle returning a row for this query:
SELECT 1 AS one_row
FROM dual
WHERE EXISTS (SELECT * FROM view_abcd)
AND NOT EXISTS (SELECT * FROM view_abcd)
;
This is a logical contradiction. EXISTS returns true or false depending on whether its subquery returns any rows, regardless of the values in those rows. If one predicate is true, the other must be false.
The view combines OUTER APPLY, an inner ANSI JOIN, and a correlated FETCH FIRST 1 ROW ONLY. The question reports the problem on 19c and 21c.
On Oracle 23ai Free 23.9.0.25.07, the default result is correct. Disabling 35915968 makes it return the wrong row: db<>fiddle.
I also reproduced it on 26ai Free 23.26.3.0.0 with:
ALTER SESSION SET optimizer_features_enable = '21.1.0';
That was a starting point, not the solution. I wanted a finer setting than changing the whole optimizer compatibility level.
Copilot, guided by experience
I used Copilot for this investigation. Not just to ask “what is the answer?”, but to run the experiments I would normally run myself.
Here are some of my prompts:
“Please find the bug published by Oracle.”
“Is it ANSI join? There’s a long history of ANSI joins implemented as transformations and not working.”
“Reproduce and use Pathfinder to find the exact parameter or bug control to avoid it.”
“Maybe you have run without the latest patches.”
“I’m able to reproduce the bug on 26ai with
optimizer_features_enable='21.1.0', but I would like a finer setting.”
The agent ran Mauro Pagano’s Pathfinder with OFE 21.1 as the baseline, testing 2,811 cases. I described this method in the past (I ‘fixed’ execution plan regression with optimizer_features_enable, what to do next?).
Two controls avoided the wrong result: 35915968:1 and 35969400:1. The description of the first immediately connected the result to the FETCH FIRST rewrite.
Then I prompted Copilot for the next step:
“Look at the execution plans.”
“Is it a combination of
FETCH FIRSTtransformation, ANSI join, and merged views?”
This is where AI assistance is useful to me. My experience guides the investigation. The agent handles the repetitive experiments, collects the output, and helps compare it. The results still have to explain the behavior. A plausible answer is not enough.
What the trace adds
The failing execution plan is reduced to an unconditional FAST DUAL. The correct plan retains the filter, the outer-join branches, and the COUNT STOPKEY operations.
The expanded SQL and execution plans show the difference. The paired 10053 traces add something more interesting: the analytic and ROWNUM rewrites lead to different view-merging decisions.
In the analytic path, Oracle keeps the window-function query block:
SVM: SVM bypassed on view SEL$4(#0): Window functions in this view.
It also retains an additional correlated lateral-view layer:
SVM: SVM bypassed on view SEL$F23444D6(#0): Lateral view with left correlation.
In the ROWNUM path, Oracle merges the simple inner query into the block containing the row limit:
CVM: Merging SPJ view SEL$4 (#0) into SEL$6 (#0)
The resulting scalar subquery is simpler:
SELECT "D"."DUMMY"
FROM "SYS"."DUAL" "D"
WHERE ROWNUM <= 1
AND "D"."DUMMY" = "A"."DUMMY"
It also merges a layer that remained separate in the analytic path:
CVM: Merging SPJ view SEL$F23444D6 (#0) into SEL$3 (#0)
So the explanation is not simply “ROWNUM prevents view merging.” The correct path actually performs more merges at these points. It removes unnecessary wrapper blocks while retaining the scalar subquery’s row-limit boundary.
Both paths still have restrictions around the null-augmented outer-joined lateral view. The analytic path adds another combination of boundaries and correlations, and that combination ends in the wrong executable plan.
This identifies the problematic path. It does not identify the exact line of Oracle code responsible, nor prove that 35915968 was originally created to fix this particular report. What we have demonstrated is that enabling this transformation avoids the wrong result.
New syntax, old machinery
I like standard SQL syntax. FETCH FIRST expresses the intention more clearly than a manually nested ROWNUM query. I've seen too many of those queries with the wrong placement of ORDER BY.
But syntax support and native implementation are different things.
Oracle’s ANSI joins are translated into internal structures, including lateral views and legacy outer-join representations. The row-limit clause was translated into an analytic query. Each transformation may be reasonable on its own. Their combinations are where the complexity grows.
There is a long history of ANSI-related wrong-results bugs. Jonathan Lewis has documented examples involving ANSI joins and NATURAL JOIN. I've see it striking again with materialized views and assertions.
Transformations are essential to query optimization. The problem is not that they exist. It is that adding a feature through several layers of rewriting also adds interactions that must preserve the original semantics.
Here, the legacy ROWNUM implementation has two advantages: first-k-row optimization and a row-limit boundary that Oracle already knows how to handle. Finally, the newer syntax stayed but the machinery underneath returned to the simpler implementation.
And AI can help us investigate that machinery. Not by replacing database knowledge with a confident explanation, but by making it easier to run the experiments that turn an explanation into evidence. You don't know to download Pathfinder and run it yourself when an AI agent can reproduce everything on a docker container.
What about PostgreSQL?
You may wonder how PostgreSQL dealt with the addition of FETCH FIRST to the SQL standard. Unlike Oracle’s initial implementation, PostgreSQL did not rewrite it into an analytic ROW_NUMBER() query. FETCH FIRST was implemented as an alternative syntax for LIMIT/OFFSET which already existed, and EXPLAIN shows a Limit node. The planner also knows the requested row count when costing paths: if an index supplies the required order, it can choose that path and stop once enough rows have been produced. Otherwise, it may still need to scan or sort more data.
At the top level, FETCH FIRST does not add a query block. Inside a subquery, though, the row limit prevents that subquery from being pulled up, since moving the limit could change which rows it returns. So PostgreSQL has a native stop-after-N boundary, conceptually like Oracle’s COUNT STOPKEY—not the same operator, but the same basic idea. The engines converge on that approach. PostgreSQL used it from the start, while Oracle initially implemented FETCH FIRST through ROW_NUMBER() analytic filter and later changed the rewrite for the simple row-limit case.
