Re: any ideas for better performance of this query ?

<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