Re: How can I get the first 20 records !!

Select .... where (rownum <= 20);

"Ralf" <Miko_at_boehrer.de> wrote in message news:fa624200.0303190707.2652ad4c_at_posting.google.com...
> 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:48:24 CET