Oracle 12c – FETCH FIRST n ROWS ONLY

Oracle 12c offers a lot of fascinating new features – among them (at last) "FETCH FIRST n ROWS ONLY" syntax.

http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php

Out of curiosity I’ve checked with 10053 event, the following query:

select first_name, last_name, department_name
  from employees e1, departments d
  where e1.department_id=d.department_id
  fetch first 10 rows only

And this was the result 🙂

SELECT "from$_subquery$_003"."FIRST_NAME" "FIRST_NAME",
  "from$_subquery$_003"."LAST_NAME" "LAST_NAME",
  "from$_subquery$_003"."DEPARTMENT_NAME" "DEPARTMENT_NAME"
FROM
  (SELECT "E1"."FIRST_NAME" "FIRST_NAME",
    "E1"."LAST_NAME" "LAST_NAME",
    "D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
    ROW_NUMBER() OVER ( ORDER BY NULL ) "rowlimit_$$_rownumber"
  FROM "HR"."EMPLOYEES" "E1",
    "HR"."DEPARTMENTS" "D"
  WHERE "E1"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
  ) "from$_subquery$_003"
WHERE "from$_subquery$_003"."rowlimit_$$_rownumber"<=10