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

Age old problem, age old mistake in initial solution!!

Depending on what you mean by 'first 20 records', using the rownum < 20 will NOT work. Rownum is a pseudo column and is worked out after the result set is parsed. You cant use it in the where clause to narrow down.

Looking at your SQL, what you really want to do is something like the following.

SELECT a.name, a.address FROM

   (select rownum cnt, name, postcode
    from address where lastname >= ' ' and firstname > ' '     order by lastname, firstname)
WHERE a.cnt < 20;

The above *should* work depending on your Oracle version.

M

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 - 11:27:04 CET