Re: Query to get 10 oldest rows of a table

From: Mark Gumbs <mgumbs_at_nospam.hotmail.com>
Date: Tue, 22 Jun 1999 09:18:30 +0100
Message-ID: <376f4483.0@145.227.194.253>

You can either rewrite the procedure as a stored function with a cursor to return the first 10 records.

You can try a 'cheat' in your current code, compare the effective date in your outer query with some obscure date to force the outer query to use the index on effective date.
Its worth a try

select /*+FIRST_ROWS+*/ a.first_name , a.last_name, a.effective_date from client a

where 10 >=     (select /*+FIRST_ROWS+*/ count(effective_date)
                             from client b
                             where b.effective_date <= a.effective_date)

and a.effective_date > to_date('01-JAN-1900','DD-MON-YYYY') order by a.effective_date asc

Mark

Eric wrote in message <01bebc0a$b2c98a40$a60c6480_at_w15-1429.rmoc.on.ca>...
>Suppose the following client table defined as:
>first_name varchar(30)
>last_name varchar(30)
>effective_date date
>with an index on effective_date.
>
>The application would like the oldest 10 clients so I wrote the following
>query:
>
>select /*+FIRST_ROWS+*/ a.first_name , a.last_name, a.effective_date
>from client a
>where 10 >= (select /*+FIRST_ROWS+*/ count(effective_date)
> from client b
> where b.effective_date <= a.effective_date)
>order by a.effective_date asc
>;
>
>which works fine. The problem is that the query takes several minutes to
>execute... Does anyone know a better way of writing this type of query?
>
Received on Tue Jun 22 1999 - 03:18:30 CDT