Re: How can I get the first 20 records !!
Date: 19 Mar 2003 16:53:04 -0800
Message-ID: <130ba93a.0303191653.681cb230_at_posting.google.com>
What ORACLE version? Are you using CBO? In the worst case, you can use stored outline to stablize the execution plan. But I don't think it is necessary in your case. A coulpe of thoughts:
- Include "order by" in your first query: select * from address where lastname >= ' ' and firstname > ' ' and rownum <= 20 order by lastname, firstname;
For a table with 1 million records, I don't see why ORACLE would not choose the the index idx_lastname_firstname. The "order by" clause should give the CBO a hint to use the right index to avoid the expensive sort. You want to keep your statistics up to date of course.
2. I have doubt about your claim that your 2nd query is slow. If index idx_lastname_firstname is used, and sorting is avoided, it should be fast. Did you try to run a SQL trace session and check the execution plan?
- Jusung Yang
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 - 01:53:04 CET