Re: any ideas for better performance of this query ?

From: <sweidanz_at_yahoo.com>
Date: Thu, 11 Oct 2001 18:47:48 +1000
Message-ID: <W1dx7.2$in1.431@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.
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

       full scan m (which is only 100 records)
       index b (if the index is the joined column)

index s

Thats just my thoughts. Run the execution plan and you will see the actual result.

hope that helps,
ZS Received on Thu Oct 11 2001 - 03:47:48 CDT