{"id":3705,"date":"2014-05-05T05:24:00","date_gmt":"2014-05-05T03:24:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/"},"modified":"2014-05-05T05:24:00","modified_gmt":"2014-05-05T03:24:00","slug":"oracle-rownum-vs-rownumber-and-12c-fetch-first","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/","title":{"rendered":"ROWNUM vs ROW_NUMBER() and 12c fetch first"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nPrior to Oracle 12c, there were two ways to do &#8216;top-n&#8217; queries:<\/p>\n<ul>\n<li>use rownum after sorting rows with &#8220;order by&#8221;<\/li>\n<li>use row_number() over (order by)<\/li>\n<\/ul>\n<p>Top-n queries are usually required for result pagination. The application shows the first 10 rows, then the next 10 on the following page, etc. In these queries, user interactions are often stateless, so we cannot just open a cursor and fetch 10 rows on each user request.<\/p>\n<p>In both case you will need a subquery. In the former case you need it because otherwise the order by will be applied after the rownum filter. In the latter case you need it because you cannot use analytic functions in the where clause.<\/p>\n<p>But there is a big difference, which is very important to get the optimal plan, and this is what I will expose here before showing the new 12c syntax.<\/p>\n<h3>Query with rownum<\/h3>\n<pre><code>PLAN_TABLE_OUTPUT\n SQL_ID\u00a07x2wat0fhwdn9,\u00a0child\u00a0number\u00a00\n \u00a0-------------------------------------\n \u00a0select\u00a0*\u00a0from\u00a0(\n \u00a0\u00a0\u00a0\u00a0select\u00a0*\u00a0from\u00a0test\u00a0where\u00a0contract_id=500\n \u00a0\u00a0\u00a0\u00a0order\u00a0by\u00a0start_validity\n \u00a0)\u00a0where\u00a0rownum &lt;=10\n \u00a0order by start_validity\n \u00a0Plan\u00a0hash\u00a0value:\u00a02207676858\n \u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0|\u00a0Buffers\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a014\u00a0|\n \u00a0|*\u00a0\u00a01\u00a0|\u00a0\u00a0COUNT\u00a0STOPKEY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a014\u00a0|\n \u00a0|\u00a0\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a014\u00a0|\n \u00a0|\u00a0\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0TABLE\u00a0ACCESS\u00a0BY\u00a0INDEX\u00a0ROWID|\u00a0TEST\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a014\u00a0|\n \u00a0|*\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0INDEX\u00a0RANGE\u00a0SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0TEST_PK\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a016\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n \u00a0Predicate\u00a0Information\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n \u00a0---------------------------------------------------\n \u00a01\u00a0-\u00a0filter(ROWNUM&lt;=10)\n \u00a04\u00a0-\u00a0access(\"CONTRACT_ID\"=500)<\/code><\/pre>\n<p>This is good. The optimizer knows that we retrieve only 10 rows and the index access was chosen for that because it returns rows in the right order without the need to sort them.<\/p>\n<h3>Query with row_number()<\/h3>\n<pre><code>\u00a0PLAN_TABLE_OUTPUT\n \u00a0SQL_ID\u00a0gmmz59f64zftf,\u00a0child\u00a0number\u00a00\n \u00a0-------------------------------------\n \u00a0select\u00a0*\u00a0from\u00a0(\n \u00a0\u00a0\u00a0select\u00a0test.*,row_number()over(order\u00a0by\u00a0start_validity)\u00a0rn\u00a0\n \u00a0\u00a0\u00a0from\u00a0test\u00a0where\u00a0contract_id=500\n \u00a0)\u00a0where\u00a0rn &lt;=10\n\u00a0order by start_validity\n\u00a0Plan\u00a0hash\u00a0value:\u00a01795822849\n\u00a0------------------------------------------------------------------------------\n \u00a0|\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0|\u00a0Buffers\u00a0|\n \u00a0------------------------------------------------------------------------------\n \u00a0|\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a04397\u00a0|\n \u00a0|*\u00a0\u00a01\u00a0|\u00a0\u00a0VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a04397\u00a0|\n \u00a0|*\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0WINDOW\u00a0SORT\u00a0PUSHED\u00a0RANK|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a02000\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a04397\u00a0|\n \u00a0|*\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0TABLE\u00a0ACCESS\u00a0FULL\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0TEST\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a02000\u00a0|\u00a0\u00a0\u00a02000\u00a0|\u00a0\u00a0\u00a0\u00a04397\u00a0|\n \u00a0------------------------------------------------------------------------------\n\u00a0Predicate\u00a0Information\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n \u00a0---------------------------------------------------\n\u00a01\u00a0-\u00a0filter(\"RN\"&lt;=10)\u00a0\n\u00a02\u00a0-\u00a0filter(ROW_NUMBER()\u00a0OVER\u00a0(\u00a0ORDER\u00a0BY\u00a0\"START_VALIDITY\")&lt;=10)\u00a0\n\u00a03\u00a0-\u00a0filter(\"CONTRACT_ID\"=500)<\/code><\/pre>\n<p>This is not good. A full table scan reads all rows, whereas an index can give those 10 rows quickly. Reading and sorting thousands of rows instead of just reading a dozen. So why the index scan was not chosen? In that case I usually force the plan to the one that I expect, so that I can see which bad estimation has driven the CBO choice. Let&#8217;s see the execution plan when forcing range scan :<\/p>\n<pre><code>\u00a0PLAN_TABLE_OUTPUT\n \u00a0SQL_ID\u00a0axxrwar6s9275,\u00a0child\u00a0number\u00a00\n \u00a0-------------------------------------\n \u00a0select\u00a0*\u00a0from\u00a0(\n \u00a0\u00a0\u00a0select\u00a0\/*+\u00a0index(TEST)\u00a0*\/\u00a0test.*,row_number()over(order\u00a0by\u00a0start_validity)\u00a0rn\u00a0\n \u00a0\u00a0\u00a0from\u00a0test\u00a0where\u00a0contract_id=500\n\u00a0) where rn&lt;=10\n\u00a0order by start_validity\n \u00a0Plan\u00a0hash\u00a0value:\u00a01912639229\n\u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0|\u00a0Buffers\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a01\u00a0|\u00a0\u00a0VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0WINDOW\u00a0NOSORT\u00a0STOPKEY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a02000\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|\u00a0\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0TABLE\u00a0ACCESS\u00a0BY\u00a0INDEX\u00a0ROWID|\u00a0TEST\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a02000\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a011\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0INDEX\u00a0RANGE\u00a0SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0TEST_PK\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a02000\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a011\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n\u00a0Predicate\u00a0Information\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n \u00a0---------------------------------------------------\n\u00a01\u00a0-\u00a0filter(\"RN\"&lt;=10)\n\u00a02\u00a0-\u00a0filter(ROW_NUMBER()\u00a0OVER\u00a0(\u00a0ORDER\u00a0BY\u00a0\"START_VALIDITY\")&lt;=10)\n \u00a04\u00a0-\u00a0access(\"CONTRACT_ID\"=500)<\/code><\/pre>\n<p>I didn&#8217;t show the cost here. The estimated number of rows is sufficient to understand. The optimizer has estimated the cost of the index access without knowing that we retreive at most 10 rows. When using rownum &lt; 10, the optimizer implicitly add a first_rows(10). But not with row_number().<\/p>\n<p>With the row_number() we have to do it ourselves:<\/p>\n<pre><code>PLAN_TABLE_OUTPUT\n \u00a0SQL_ID\u00a032m4yadk1ypsn,\u00a0child\u00a0number\u00a00\n \u00a0-------------------------------------\n \u00a0select\u00a0\/*+\u00a0FIRST_ROWS(10)\u00a0*\/\u00a0*\u00a0from\u00a0(\n \u00a0\u00a0\u00a0select\u00a0test.*,row_number()over(order\u00a0by\u00a0start_validity)\u00a0rn\u00a0\n \u00a0\u00a0\u00a0from\u00a0test\u00a0where\u00a0contract_id=500\n \u00a0)\u00a0where\u00a0rn&lt;=10\n\u00a0order by start_validity\u00a0\n \u00a0Plan\u00a0hash\u00a0value:\u00a01912639229\n\u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0|\u00a0Buffers\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a01\u00a0|\u00a0\u00a0VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0WINDOW\u00a0NOSORT\u00a0STOPKEY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|\u00a0\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0TABLE\u00a0ACCESS\u00a0BY\u00a0INDEX\u00a0ROWID|\u00a0TEST\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a011\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0INDEX\u00a0RANGE\u00a0SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0TEST_PK\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a011\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n\u00a0Predicate\u00a0Information\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n \u00a0---------------------------------------------------\n\u00a01\u00a0-\u00a0filter(\"RN\"&lt;=10)\u00a0\n\u00a02\u00a0-\u00a0filter(ROW_NUMBER()\u00a0OVER\u00a0(\u00a0ORDER\u00a0BY\u00a0\"START_VALIDITY\")&lt;=10)\u00a0\n\u00a04\u00a0-\u00a0access(\"CONTRACT_ID\"=500)<\/code><\/pre>\n<p>Now the index range scan is chosen, with the right cardinality estimation.<\/p>\n<p>So which solution it the best one? I prefer row_number() for several reasons:<\/p>\n<ul>\n<li>I like analytic functions. They have larger possibilities, such as setting the limit as a percentage of total number of rows for example.<\/li>\n<li>11g documentation for rownum says:<br \/>\nThe ROW_NUMBER built-in SQL function provides superior support for ordering the results of a query<\/li>\n<li>12c allows the ANSI syntax ORDER BY&#8230;FETCH FIRST&#8230;ROWS ONLY which is translated to row_number() predicate<\/li>\n<li>12c documentation for rownum adds:<br \/>\nThe row_limiting_clause of the SELECT statement provides superior support<\/li>\n<li>rownum has <a href=\"http:\/\/jonathanlewis.wordpress.com\/2008\/11\/11\/first_rows_n\/trackback\">first_rows_n issues<\/a> as well<\/li>\n<\/ul>\n<p>As you can see, Oracle does not say that rownum is de-supported. Just that there is another function with &#8216;superior support&#8217;. Which is the same I presume.<\/p>\n<p>So, I use row_number() in 11g or the ANSI syntax in 12c but in both cases I have to add the FIRST_ROWS(n) hint in orde rto get the right plan. Warning: don&#8217;t use the old FIRST_ROWS hint anymore which was rule based and is deprecated. The right way to tell Oracle that you will fetch only n rows is the FIRST_ROWS(n) hint.<\/p>\n<p>Now here is the same query with the 12c syntax.<\/p>\n<h3>Query with 12c syntax<\/h3>\n<pre><code>PLAN_TABLE_OUTPUT\n \u00a0SQL_ID\u00a049m5a3f33cmd0,\u00a0child\u00a0number\u00a00\n \u00a0-------------------------------------\n \u00a0select\u00a0\/*+\u00a0FIRST_ROWS(10)\u00a0*\/\u00a0*\u00a0from\u00a0test\u00a0where\u00a0contract_id=500\u00a0\n \u00a0order\u00a0by\u00a0start_validity\u00a0fetch\u00a0first\u00a010\u00a0rows\u00a0only\n\u00a0Plan\u00a0hash\u00a0value:\u00a01912639229\n\u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0Id\u00a0\u00a0|\u00a0Operation\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0Name\u00a0\u00a0\u00a0\u00a0|\u00a0Starts\u00a0|\u00a0E-Rows\u00a0|\u00a0A-Rows\u00a0|\u00a0Buffers\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n \u00a0|\u00a0\u00a0\u00a00\u00a0|\u00a0SELECT\u00a0STATEMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a01\u00a0|\u00a0\u00a0VIEW\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a02\u00a0|\u00a0\u00a0\u00a0WINDOW\u00a0NOSORT\u00a0STOPKEY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|\u00a0\u00a0\u00a03\u00a0|\u00a0\u00a0\u00a0\u00a0TABLE\u00a0ACCESS\u00a0BY\u00a0INDEX\u00a0ROWID|\u00a0TEST\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a010\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a011\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a015\u00a0|\n \u00a0|*\u00a0\u00a04\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0INDEX\u00a0RANGE\u00a0SCAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0TEST_PK\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a011\u00a0|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a04\u00a0|\n \u00a0--------------------------------------------------------------------------------------\n\u00a0Predicate\u00a0Information\u00a0(identified\u00a0by\u00a0operation\u00a0id):\n \u00a0---------------------------------------------------\n\u00a01\u00a0-\u00a0filter(\"from$_subquery$_002\".\"rowlimit_$$_rownumber\" &lt;=10)\n\u00a02\u00a0-\u00a0filter(ROW_NUMBER()\u00a0OVER\u00a0(\u00a0ORDER\u00a0BY\u00a0\"TEST\".\"START_VALIDITY\") &lt;=10 ) \u00a0\n 4\u00a0-\u00a0access(\"CONTRACT_ID\"=500)<\/code><\/pre>\n<p>The 12c syntax has been translated with row_number() predicates and I added the FIRST_ROWS(10) hint in order to have the right estimations.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . Prior to Oracle 12c, there were two ways to do &#8216;top-n&#8217; queries: use rownum after sorting rows with &#8220;order by&#8221; use row_number() over (order by) Top-n queries are usually required for result pagination. The application shows the first 10 rows, then the next 10 on the following page, etc. In these [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[217,209,98],"type_dbi":[],"class_list":["post-3705","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-cost-based-optimizer","tag-oracle-12c","tag-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>ROWNUM vs ROW_NUMBER() and 12c fetch first - dbi Blog<\/title>\n<meta name=\"description\" content=\"top-n queries with rownum and row_number() and new Oracle 12c syntax\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"ROWNUM vs ROW_NUMBER() and 12c fetch first\" \/>\n<meta property=\"og:description\" content=\"top-n queries with rownum and row_number() and new Oracle 12c syntax\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-05-05T03:24:00+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"ROWNUM vs ROW_NUMBER() and 12c fetch first\",\"datePublished\":\"2014-05-05T03:24:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/\"},\"wordCount\":576,\"commentCount\":0,\"keywords\":[\"Cost Based Optimizer\",\"Oracle 12c\",\"SQL\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/\",\"name\":\"ROWNUM vs ROW_NUMBER() and 12c fetch first - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2014-05-05T03:24:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"top-n queries with rownum and row_number() and new Oracle 12c syntax\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-rownum-vs-rownumber-and-12c-fetch-first\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"ROWNUM vs ROW_NUMBER() and 12c fetch first\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"ROWNUM vs ROW_NUMBER() and 12c fetch first - dbi Blog","description":"top-n queries with rownum and row_number() and new Oracle 12c syntax","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/","og_locale":"en_US","og_type":"article","og_title":"ROWNUM vs ROW_NUMBER() and 12c fetch first","og_description":"top-n queries with rownum and row_number() and new Oracle 12c syntax","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/","og_site_name":"dbi Blog","article_published_time":"2014-05-05T03:24:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"ROWNUM vs ROW_NUMBER() and 12c fetch first","datePublished":"2014-05-05T03:24:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/"},"wordCount":576,"commentCount":0,"keywords":["Cost Based Optimizer","Oracle 12c","SQL"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/","name":"ROWNUM vs ROW_NUMBER() and 12c fetch first - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-05-05T03:24:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"top-n queries with rownum and row_number() and new Oracle 12c syntax","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-rownum-vs-rownumber-and-12c-fetch-first\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"ROWNUM vs ROW_NUMBER() and 12c fetch first"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3705","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3705"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3705\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3705"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3705"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3705"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3705"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}