Re: Can I use an index to get the last n records?

Giganews Newsgroups
Subject: Re: Can I use an index to get the last n records?
Posted by:  fitzjarre… (fitzjarre…
Date: Mon, 16 Jul 2007

On Jul 16, 10:00 am, Timasmith <timasm.…> wrote:
> If I have a table with 100,000,000 rows
> sales(sales_id, sales_person_id, sales_date_time, ...)
> create index sales_date_index on sales (sales_person_id,
> sales_date_time);
> I want to execute something like this (which doesnt work at all)
> select * from sales
> where sales_person_id = :sales_person_param
>    and rownum < 10
> order by sales_date_time desc
> I want it to use my sales_date_index to read just 10 indexed records -
> NOT read all records for the sales_person_id, sort in memory and then
> return me the result set.
> Anyway in general I can force traversal of the records using an index?
> thanks

Obviously this 'doesn't work', and this has been discussed numerous
times before in this newsgroup.  Since searching the archives is
apparently not to your liking:

The 'problem' with your original query is that you don't fully
comprehend HOW rownum is assigned; it's assigned as the rows are
returned, not after they are ordered.  To get past that one must do

select * from
(select * from sales
where sales_person_id = :sales_person_param
  order by sales_date_time desc)
where rownum <= 10;

Now your rownum values will be assigned to the ordered result set of
the imbedded query.  Of course since your index is non-unique and the
date is the second column in your index I doubt very seriously it
would help in the ordering of your results.  The above query, though,
should return the results you expect.

David Fitzjarrell



In response to

Can I use an index to get the last n records? posted by Timasmith on Mon, 16 Jul 2007