Select `n` last inserted records in table - oracle

Question

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

What is the fastest way to select last n inserted records in Oracle, ordered by id in descending order (last inserted on top)?

n is some relatively small number - number of records to display on page - probably not bigger than 50.

Table now has 30.000.000 records with 10-15 thousands of new records daily.

Database is Oracle 10g.

Edit:
In answer to one comment: This question was motivated with execution plan for query:

  select * from MyTable order by primarykeyfield desc

Execution plan was:

--------------------------------------------- 
| Id  | Operation          | Name        |     
---------------------------------------------  
|   0 | SELECT STATEMENT   |             |
|   1 |  SORT ORDER BY     |             |
|   2 |   TABLE ACCESS FULL| MyTable     |
---------------------------------------------  

I was surprised that Oracle wants to perform full table scan and sorting when it has index on sort field.

Query from accepted answer uses index and avoids sort.

Edit 2:
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:

select * from (select * from arh_promjene order by promjena_id desc) x 
   where rownum < 50000000

uses index instead of full table access and sort (notice condition rownum < 50.000.000 - 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:

| 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<50000000)                         

It was unusual to me that Oracle is creating different execution plans for these two queries that essentially return same result set.

Edit 3: Re Amoq's comment:

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.

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?

Answer 1

Use ROWNUM:

select
  *
from
  (
    select
      *
    from
      foo
    order by
      bork
   ) x
where
  ROWNUM <= n

Note that rownum is applied before sorting for a subquery, that's why you need the two nested queries, otherwise you'll just get n random rows.

Answer 2

In cases where you don't have a strictly increasing field, you could also use ORA_ROWSCN (system change number) as an approximation of this.

select * from (select * from student order by ORA_ROWSCN desc) where rownum<10

Caution: this is not exact, since Oracle records only one SCN per block, not per row. Also it seems to do a full table scan - probably oracle is not smart enough to optimize this kind of sort. So this might not be a good idea for production use.

Answer 3

Will it be viewed many more times than it is updated? How about keeping another table of the IDs of the last N inserted rows (use a trigger to delete the smallest ID from this table and add a new row with the current-inserted).

You now have a table that records the IDs of the last N inserted rows. Any time you want the N, just join it to the main table. If N changes, pick the max it can be, and then filter it after... of course you may find it not so fast for your app (maintenance of this table may negate any performance gain)

Answer 4

This may help you if you don't know the name of the fields or anything other than table name....

select * from (
  select * from(
    select rownum r,student.* from student where rownum<=(
      select max(rownum) from student
    )
  ) order by r desc
 ) where r<=10;

Answer 5

Try doing a index_desc hint

select /*+ index_desc(MyTable,<PK_index>) */ * from MyTable order by primarykeyfield desc