Re: Oracle Query Tuning

My experience is, that the C.B. Optimizer can get a little confused, if you specify all restrictions you know to the tables. Try specifying only those that are not redundant, that means:
1) know the best table to start with (the 'driving' one) and specify the restrictions you need for achieving a correct result. Probably test it seperate.
2) join the correct 2nd table only by applying the absolutely minimal needed join-conditions and add some filter-restrictions (to throw away the returned records you dont' want)
3) do the same with the 3rd table.

If that all won't help, arrange the tables in the correct order and apply the /*+ordered*/ - optimizer hint.

If your join-conditions are too weak, then the optimizer always will decide to do a sort-merge or a hash (only >7.3) join to the two record-sets forming a join. In that case it can be better to apply more than the minimal set of restrictions, depending on the indices upon your tables.

Try the explain plan feature.

Ingo

manoj.lahoti_at_gepex.ge.com wrote:

> I'm trying run a select query against 3 oracle tables with 250,000 ,45000
> and 5000 records respectively. I have indexes on almost all the fields
> that i'm using in where clause of the query. Now this query took about 30
> min to fetch the results when I tried to create index on a field which
> has only 2 values i.e. 'Y' and 'N, otherwise it takes about 10 -15 min to
> run this query. Any suggestions to improve the query response would be
> appreciated.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Tue Feb 17 1998 - 00:00:00 CST