Fix control 35915968 sends Oracle 23ai's FETCH FIRST rewrite back to ROWNUM

Oracle has never executed FETCH FIRST as written. It rewrites the clause into constructs the database already had, and in 12c it picked the analytic ROW_NUMBER() form ^[3]. Those are the two shapes developers wrote by hand before 2013. One orders rows in a subquery and filters ROWNUM outside. The other computes ROW_NUMBER() OVER (ORDER BY ...) in a subquery and filters it outside ^[4]. Either way there is an inline view, and the 26ai plan still shows one ^[13].

The 12c choice had a cost. According to a dev.to post that traces the history, a ROWNUM <= 10 predicate told the optimizer it needed ten rows, so it could cost an ordered index access for ten rows ^[6]. The analytic rewrite could be costed as if many more rows were needed, and a full scan plus sort then looked cheaper ^[6]. The author's 2014 advice was a FIRST_ROWS(n) hint, not the old FIRST_ROWS ^[7]. 19c removed the need with fix control 22174392, described as "first k row optimization for window function rownum predicate" ^[8]. Plans showed the fix as WINDOW NOSORT STOPKEY where they had shown WINDOW SORT PUSHED RANK ^[9].

The return to ROWNUM is another fix control. In v$system_fix_control, bug 35915968 is described as "fetch first transformation using rownum" and tagged with optimizer_feature_enable 23.1.0 ^[10]. The post found it in the 26ai home's rdbms/admin/bundlefcp_DBBP.xml, under the 23.4.0.0.0 bundle, as `<fix_control default_value="1">35915968</fix_control>` ^[11]. It is on by default. Early 23c releases still used ROW_NUMBER() ^[2]. The new suffix did not do it: "ai" replaced "c" at 23.4, and 26ai is still the 23 release family ^[12].

Shipping a behaviour change as a named control with a description and a compatibility tag is good engineering. A DBA can query the database to find out which rewrite it applies ^[10].

On 26ai, the plan for SELECT * FROM dual ORDER BY dummy FETCH FIRST 42 ROWS ONLY is COUNT STOPKEY over a VIEW over TABLE ACCESS FULL DUAL ^[13]. On 19c with fix 22174392, the analytic form shows a WINDOW stopkey ^[9]. A simple Top-N query moving from 19c to 23ai therefore changes operation names in its plan ^[1]. The post notes that Oracle can stop early when an index supplies the order, and may still need a sort without one ^[14].

Run time is a separate question. ROWNUM predicates always got first-k-row costing, and 19c extended it to the window-function form, so the 12c costing gap is closed under both rewrites ^[2]. The difference that remains is structural. ROWNUM acts as a non-mergeable view barrier, so the optimizer treats the query block as an isolated inline view ^[15]. I'd expect most standalone Top-N queries to keep their access path under a new operation name. The cases to retest are Top-N blocks inside larger statements, where the barrier limits what the optimizer can merge ^[15]. The evidence so far is the simple row-limit case on the one-row DUAL table ^[2][13].