Re: Selecting last n rows

What do you mean under "last rows"?
There is no "last" or "first" rows in RDB theory. All rows are the same. If you need range rows you must add any feature to your entity - add column to table.

The simple samples with ROWNUM will give you rows by order of phisical storage in segment. Oracle does not garant the rows will stored in order they inserted.
If you have any first row deleteted, there will a gap (free space) in segment. And Oracle will
try to use this space next time you insert other "latest" row.

If you need select last rows by time of insertion, you have to add a column sort of "Timestamp" or "Sequenced Number" and fill it inside trigger by SYSDATE or Sequence.NextVal.

After this you can use hint

select /*+ index_desc (table1 idx_timestamp) */ * from Table1
where rownum<=10

Hint index_desc garants the optimizer must use index. If optimizer will not use index (or you have not such index)  this will select 10 of rows before sorting. In this case queries like
select * from table1 where rownum<=10 order by timestamp; may return wrong result. Because Oracle will, at first, get 10 rows in phisical order, and then sort them.

Regards,
Mark

Himanshu Gupta <himanshu.gupta_at_luton.ac.uk> wrote in message news:3779D104.C4EFC45C_at_luton.ac.uk...
> How can I select last 10 rows from a table
> having more that 10,000 records?
>
> Thanks.
> Himanshu.
Received on Wed Jun 30 1999 - 08:25:17 CDT