The FETCH FIRST story: from ROW_NUMBER() in 12c back to ROWNUM in 23ai, and a LIMIT equivalent for PostgreSQL

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 ROWNUM outside:

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. However, 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 and is just a marketing suffix following the tech hype cycles (_i_nternet, _g_rid, _c_loud, and now ai). It's not a major version change either: Oracle 18c and 19c belong to the 12cR2 release family, with names changed to the release year. Similarly, 26ai is still in the 23 major release family, and “ai” replaced “c” with the 23.4 minor version.

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 change is transparent to the API, as applications still use the same syntax, but the implementation is important to understand when it comes to troubleshoot results or performance. Fortunately, Oracle Database provides the right tools to the developer, especially dbms_xplanand dbms_utility.

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.

dbi BlogROWNUM vs ROW_NUMBER() and 12c fetch first - dbi Blog

top-n queries with rownum and row_number() and new Oracle 12c syntax

dbi-services.com

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.

I've also written about a remaining performance issue on Exadata, fixed in 12.2 Oracle Exadata – poor optimization for FIRST_ROWS

dbi BlogOracle Exadata - poor optimization for FIRST_ROWS - dbi Blog

TABLE ACCESS STORAGE FULL FIRST ROWS: 'smart scan optimization for fast response' is not always a good optimization.

dbi-services.com

This was further 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.

dbi Blog19c: scalable Top-N queries without further hints to the query planner - dbi Blog

dbi-services.com

The improvement was 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. This WINDOW NOSORT STOPKEY stayed until 26ai where it became a simple COUNT STOPKEY like with the ROWNUM approach.

Now, back to ROWNUM

The first-k-row with ROWNUM 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. This case comes from a Stack Overflow user encountering an issue that is fixed when using ROWNUM.

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. NULL doesn't matter here as this operation concerns rows and not columns, similarly to COUNT(*).

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. Rather than setting a lab with Oracle Databases and install and run the troubleshooting tools, which can take hours, I used AI. The main reason I'm sharing this is to show that with AI such troubleshooting becomes much easer.

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

I didn't ask if it is a bug because I know SQL enough to know that no row can EXISTS and NOT EXISTS at the same time.

“Is it ANSI join? There’s a long history of ANSI joins implemented as transformations and not working.”

I'm not familiar enough with OUTER APPLY but my experience with ANSI joins on Oracle guided this question.

“Reproduce and use Pathfinder to find the exact parameter or bug control to avoid it.”

I didn't mention what is Pathfinder and where to find it. There are lots of online content about troubleshooting Oracle Database and the model knows about it.

“Maybe you have run without the latest patches.”

That came from my knowledge that running Oracle XE or Free edition is different from a production setup with the latest path. The AI answers where inconsistent, sometimes acting as if the lab was running with the latest patches, sometimes thinking the ALTER SESSION enabled it when ':0 actually disables it.

“I’m able to reproduce the bug on 26ai with optimizer_features_enable='21.1.0', but I would like a finer setting.”

It's good to remind the final goal. The AI though that I wanted a solution to get the right result whereas my goal was to understand exactly the scope of the problem and the solution.

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 FIRST transformation, 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 prove that 35915968 was originally created to fix this particular query shape. 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, or even missing 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 in 2008. 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 conclusion to this investigation is that 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.

In any case, I recommend always looking at the execution plan. Row limiting with FETCH FIRST, LIMIT, TOP, or ROWNUM sits at the boundary between relational operations and consuming their results: relations are unordered, and “the first N rows” has no meaning without an ordering. A client doesn’t need this syntax just to consume N rows: it can fetch from an ordered SQL cursor and stop when it has enough. Putting the limit in the query makes that requirement explicit to the database. I guess the choice of the analytic rewrite was made to be closer to the relational concepts and existing SQL operations, but a dedicated stop operation is finally more practical. Still, stopping after N rows does not necessarily mean reading only N rows—sorting may require reading them all. That is why the execution plan matters.