Re: how to tune up this query?

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 30 Jan 2003 09:14:23 -0800
Message-ID: <336da121.0301300914.3b5a39d5@posting.google.com>

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

Use analytic function row_number():

 SELECT id, name, priority FROM
 (SELECT id, name, priority,

         row_number() over (partition by id, name, priority order by stage)
         as rr 

FROM table_name
 WHERE stage = 'start' and state = 1)
 WHERE rr <= 1000;

It might be faster if stage column is indexed. Received on Thu Jan 30 2003 - 11:14:23 CST