Re: how to tune up this query?

Sorry, it's indeed a typo. It should be order by priority. Stage and state are indexed. Priority is not indexed. ID is the pk. I also suspect the "order by" cause which sort the 200K results from the subquery takes up all the time. Will an index on priority help sorting? Thanks..

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

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4008 Card=3215 Bytes
          =115740)

   1    0   VIEW (Cost=4008 Card=3215 Bytes=115740)
   2    1     SORT (ORDER BY) (Cost=4008 Card=3215 Bytes=77160)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_NAME'
(Cost=3992 C
          ard=3215 Bytes=77160)

   4    3         AND-EQUAL
   5    4           INDEX (RANGE SCAN) OF 'IN_TN_STAGE' (NON-UNIQU
          E) (Cost=57 Card=3215)

   6    4           INDEX (RANGE SCAN) OF 'IN_TN_STATE' (NON-UNIQU
          E) (Cost=918 Card=3215)

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3e38e92e$0$230$ed9e5944_at_reading.news.pipex.net>...
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
> news:v3hdqlh378ru79_at_corp.supernews.com...
> > Why are we ordering by stage when stage = 'start'?. Remove the order by
> clause, put proper indexes ...
> > and you should be happier.
>
> I also thought this, but suspect that it is a typo and should read ORDER BY
> priority ASC, since the text description refers to retrieving the 1000
> lowest priority records.
>
> I agree about seeing an explain plan though.
Received on Thu Jan 30 2003 - 11:24:15 CST