Re: any ideas for better performance of this query ?

If nothing works out, try rearranging the tables in the from Clause and the Where Clause Items.

I have seen the optimizer behave weirdly when it cannto choose a proper driving table or has less information.

So first analyze your tables . If it still does not work well and you are breaking your head rearrange your From and Where Clause tables and try.

Rearranging tables should , theorotically only help in a rule based approach to tuning. But when the Cost based optimizer cannot choose a proper driving table , then it looks for the arrangement.

Lastly, its not a good practice to create indexes on the entire Where Clause. It might slower your inserts and deletes. As an example of joining emp and dept tables the smaller table(dept) should be scanned fully and the larger table(emp) needs to have an index specified.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1003255938.3496.0.nnrp-01.9e984b29_at_news.demon.co.uk>...
> Given that the output was clearly from autotrace,
> and the lines in the execution path had no 'cost'
> or 'card' elements, even though several tables
> were listed, I would say that the original assumption
> was a perfectly reasonable one to make.
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
> Screen saver or Life saver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>
> Niall Litchfield wrote in message
> <3bcc0262$0$227$ed9e5944_at_reading.news.pipex.net>...
> ><sweidanz_at_yahoo.com> wrote in message
> >news:Zryx7.10$cH1.347_at_nsw.nnrp.telstra.net...
> >> Can you please explain how you read it that way and how you knew its
> >> cost-based method? If the optimiser is CHOOSE it does not mean Oracle is
> >> going use the cost-based!! If none of the tables in the query is
> analyzed,
> >> oracle is going use the rule-based. right?
> >
> >Of course you are right. If no stats exist then Oracle has to use the rule
> >based optimiser.So I should have said that stats might be "out of
> >date,innaccurate or non-existent". My real gripe was with the statement "I
> >see you are using RULE. " You could only state this for sure if the explain
> >plan said Optimizer=RULE. Any other value says that Oracle will use the CBO
> >if at all possible.
Received on Thu Oct 18 2001 - 05:36:47 CDT