Re: how to tune up this query?

What's the response time from

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

HOW LONG does the first query take ?

"Susan Lam" <susana73_at_hotmail.com> wrote in message news:7186ed56.0301300924.7c709c6e_at_posting.google.com...
> 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:51:50 CST