Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to tune up this query?

Re: how to tune up this query?

From: <ctcgag_at_hotmail.com>
Date: 04 Feb 2003 20:34:51 GMT
Message-ID: <20030204153451.853$Ab@newsreader.com>


susana73_at_hotmail.com (Susan Lam) wrote:
> 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.

How much time are we talking here? I did an analgous query that took 1.25 seconds. Is that about how yours did? If you executed it frequently and 1.25 seconds is too long, I'd recommend redesigning the app so it needs to execute this query less often.

> Will an index on priority help
> sorting? Thanks..

Conceptually, it could march up the priority index returning things that match the where clause until it hits 1000. If 1/15 of the table matches the where clause, (and that ratio is independent of priority), than you'd expect to march over 15*1000=15,000 rows in order to find your 1000. Whether this would be faster than sorting, I don't know. It would depend on things like cluster factor between the table and priority index. Alas, I have been unable to hint 9iR2 into using this execution plan, so I can't do the experiment and see.

And in the execution plan below, is has (ORDER BY) for step 2, whereas I'd expect (ORDER BY STOPKEY). I'd wonder why that is. What version of oracle?

Perhaps it's because the optimizer thinks only 3215 things will qualify based on the where clause, rather than the 200,000 things that (you report) actually do qualify. Are your statistics up to date?

>
> 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.

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB
Received on Tue Feb 04 2003 - 14:34:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US