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: any ideas for better performance of this query ?

Re: any ideas for better performance of this query ?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 11 Oct 2001 15:05:54 +0100
Message-ID: <3bc5a75a$0$8508$ed9e5944@reading.news.pipex.net>


<sweidanz_at_yahoo.com> wrote in message
news:W1dx7.2$in1.431_at_nsw.nnrp.telstra.net...
> The first thing i can notice is that you are using the RULE-based method
> not the COST-based method. The rule based method is an old/un-intelligent
> method prior to ora8.

I don't read it that way.

from the original post
"
Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE "
This says COST based to me. Of course the stats on the tables may well be out of date or inaccurate.

> First thing i would ask you to do is Analyze your tables/schema and then
> re-run the query and see whats the difference.
>
> In your query you are joining m--> b-->s (s.from_state=3201)
>
> Therefore I would expect Oracle (with cost-based method) to do the
> following:
>
> nested loop
> nested loop
> full scan m (which is only 100 records)
> index b (if the index is the joined column)
> index s

That is what I'd expect as well.

This might be forced by use of the ORDERED hint in the query.

In general though I agree with Nuno. the query returns 400k in 600 trips. 2 odd seconds doesn't seem to bad for that.

You could try substituting the select clause with select 1 and see what if any difference that makes.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
Received on Thu Oct 11 2001 - 09:05:54 CDT

Original text of this message

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