Select `n` last inserted records in table - oracle

Question

The table has a surrogate primary key generated from the sequence. Unfortunately, this sequence is used to generate keys for some other tables (I have not designed it, and I cannot change it).

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

n - some relatively small number - the number of entries to display on the page - possibly not more than 50.

The table now has 30,000,000 records with 10-15 thousand new records daily.

The database is Oracle 10g.

Edit:
In response to one comment: this question was motivated by a query execution plan:

  select * from MyTable order by primarykeyfield desc 

Execution plan:

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

I was surprised that Oracle wants to perform a full table scan and sort when it has an index in the sort field.

The request from the accepted answer uses the index and allows you to sort the sort.

Edit 2:
Reynolds Number APC Comment: Sorting was the part that surprised me. I expected Oracle to use the index to retrieve rows in the expected order. Request execution plan:

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

uses an index instead of full access to the table and sorting (the notification condition rownum < 50.000.000 is more than the number of records in the table, and Oracle knows that it must retrieve all records from the table). This query returns all rows as the first query, but with the 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 for me that Oracle creates different execution plans for these two queries, which essentially return the same set of results.

Edit 3: Re Amoq comment:

Oracle does not know that 50M is greater than the number of rows. Of course, he has statistics, but they can be old and wrong - and Oracle would never allow themselves the result only because the statistics are incorrect.

Are you sure? In versions of Oracle prior to 9, it is recommended that you update statistics manually from time to time. Starting with version 10, Oracle automatically updates statistics. What is the use of statistics if Oracle does not use it to optimize queries?

Answer 1

Use ROWNUM :

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

Please note that before sorting, ROWNUM is used for the subquery, so you need two nested queries, otherwise you just get n random rows.

Answer 2

Will it be viewed much more often than it is being updated? How about saving another identifier table of the last N inserted rows (use a trigger to remove the smallest identifier from this table and add a new row with the currently inserted).

Now you have a table in which the identifiers of the last N inserted rows are written. Every time you want N, just attach it to the main table. If N is changing, select the maximum value that may be, and then filter it after ... of course, you may not find it as fast for your application (serving this table may negate any performance increase)

Answer 3

This can help you if you do not know the names of the fields or nothing but the name of the table ....

 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 4

Try hinting index_desc

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

Answer 5

In cases where you do not have a strictly growing field, you can also use ORA_ROWSCN (system shift number) as an approximation of this,

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

Caution: this is not accurate, since Oracle writes only one SCN for each block, and not for each row. It also seems to be a full table scan - perhaps the oracle is not smart enough to optimize this kind of sorting. Thus, it may not be a good idea to use in production.