How can I get the first 20 records !!

Hi to all,

I have a very simple question and hope there is an answer. I have a huge table: "address" (about 1.000.000 records):   (colums: ID, lastname, firstname)
  (indices: idx_id (=unique), idx_lastname_firstname)

The only thing I want is to get the first 20 records in the order: "lastname-firstname"
in a very short time.

I know some approaches which don't work :

1. select /*+ index (address idx_lastname_firstname) */
   • from address where lastname >= ' ' and firstname > ' ' and rownum <= 20;
     (This doesn't work properly, because the index-hint is only a hint and it's
     not guaranteed that, if I would have a similar index (e.g. idx_firstname_lastname) my supposed index is used).
2. select * from ( select * from address where lastname >= ' ' order by lastname,firstname) where rownum <= 20;
   (This does work properly, but it takes to much time to get the results
   because the inner select-statement creates a big cursor. This is the reason why I can't use this type of select-statement).

I have no more further ideas to implement my request. Please response to me if you have any idea or comment to my problem.

Thanks in advance.
 Ralf Received on Wed Mar 19 2003 - 16:07:53 CET