Re: How to optimize query's execution?

Uf, as David pointed out the Oracle version number is often important to the response.

The plan you presented looks as though it could be the optimal plan being indexed and nested looped based; however, in the absence of table and column statistics and without the actual SQL to look at it is impossible to say.

General rule
When you have a query plan you are uncertain of check the correctness of the statistics. Regenerate the statistics if necessary using a large sample size. Recheck the plan.

Look at the join order. If it does not appear to be the best order for the join to follow then rearrange the tables so that the table names follow the FROM clause in the order you think should be followed. Add an ORDEDERED hint and look at the plan to see if the CBO was able to follow the hint. Time test.

You can also try forcing a hash join instead of a nested loops join if one of the tables is an order of magnitude larger than the other.

Depending on your release and configuration choices several init.ora (spfile) parameters affect the CBO.

HTH -- Mark D Powell -- Received on Tue Aug 30 2005 - 08:56:24 CDT