Re: how to tune up this query?

Susan,
Without trying it, could this not be re-written as follows:

  SELECT id, name, priority FROM table_name   WHERE stage = 'start' and state = 1
  AND rownum <= 1000
  ORDER BY stage ASC

Remember that rownun is treated differently from other fields and will not be applied until AFTER the sort has taken place.

ETA susana73_at_hotmail.com (Susan Lam) wrote in message news:<7186ed56.0301292013.6212b21c_at_posting.google.com>...
> I have a 3 million records table. I want to retrieve the first 1000
> records of the lowest number of priority. The query below is
> extremely slow. I am suspecting it's because the subquery is
> producing 200k records and it needs to sort the 200k records.
>
> SELECT id, name, priority FROM
> (SELECT id, name, priority FROM table_name
> WHERE stage = 'start' and state = 1 ORDER BY stage ASC)
> WHERE rownum <= 1000;
>
> Is there any way to tune up this? Or should I write this query in
> another way?
>
> Thanks,
> Susan
Received on Thu Jan 30 2003 - 07:05:02 CST