title: Select `n` last inserted records in table - oracle
body: <p>Table has surrogate primary key generated from sequence. Unfortunately, this sequence is used for generating keys for some other tables (I did not designed it and I cannot change it).</p>
  
  <p>What is the fastest way to select last <code>n</code> inserted records in Oracle, ordered by id in descending order (last inserted on top)?  </p>
  
  <p><code>n</code> is some relatively small number - number of records to display on page - probably not bigger than 50.</p>
  
  <p>Table now has 30.000.000 records with 10-15 thousands of new records daily.</p>
  
  <p>Database is Oracle 10g.</p>
  
  <p><strong>Edit:</strong><br>
  In answer to one comment: This question was motivated with execution plan for query:</p>
  
  <pre><code>  select * from MyTable order by primarykeyfield desc
  </code></pre>
  
  <p>Execution plan was:</p>
  
  <pre><code>--------------------------------------------- 
  | Id  | Operation          | Name        |     
  ---------------------------------------------  
  |   0 | SELECT STATEMENT   |             |
  |   1 |  SORT ORDER BY     |             |
  |   2 |   TABLE ACCESS FULL| MyTable     |
  ---------------------------------------------  
  </code></pre>
  
  <p>I was surprised that Oracle wants to perform full table scan and sorting when it has index on sort field.</p>
  
  <p>Query from accepted answer uses index and avoids sort.</p>
  
  <p><strong>Edit 2:</strong><br>
  Re. APC's comment: Sorting was part that surprised me. I expected that Oracle would use index to retrieve rows in expected order. Execution plan for query:</p>
  
  <pre><code>select * from (select * from arh_promjene order by promjena_id desc) x 
     where rownum &lt; 50000000
  </code></pre>
  
  <p>uses index instead of full table access and sort (notice condition <code>rownum &lt; 50.000.000</code> - this is way more than number of records in table and Oracle knows that it should retrieve all records from table). This query returns all rows as first query, but with following execution plan:</p>
  
  <pre><code>| Id  | Operation                     | Name         | 
  -------------------------------------------------------
  |   0 | SELECT STATEMENT              |              | 
  |*  1 |  COUNT STOPKEY                |              | 
  |   2 |   VIEW                        |              | 
  |   3 |    TABLE ACCESS BY INDEX ROWID| MyTable      | 
  |   4 |     INDEX FULL SCAN DESCENDING| SYS_C008809  | 
  
  Predicate Information (identified by operation id):    
  ---------------------------------------------------    
  
     1 - filter(ROWNUM&lt;50000000)                         
  </code></pre>
  
  <p>It was unusual to me that Oracle is creating different execution plans for these two queries that essentially return same result set.  </p>
  
  <p><strong>Edit 3:</strong>
  Re Amoq's comment:</p>
  
  <blockquote>
    <p>Oracle doesn't know that 50M is
    greater than the number of rows. Sure,
    it has statistics, but they could be
    old and wrong - and Oracle would never
    allow itself to deliver an incorrect
    result only because the statistics are
    wrong.</p>
  </blockquote>
  
  <p>Are you sure? In Oracle versions up to 9 it was recommended to manually refresh statistics from time to time. Since version 10 Oracle automatically updates statistics. What's the use of statistics data if Oracle does not use it for query optimization? </p>