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

Use the first query but add the "orderby" bit too. This will guarantee the correct results. Lets face it, it is better to get the correct results even if slower than 100% ideal than it is to get the wrong results altogether!!

ETA Miko_at_boehrer.de (Ralf) 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 Thu Mar 20 2003 - 09:59:47 CET