Re: Oracle Cost-Based Optimizer

Some general remarks:
First of all make sure you have updated statistics in your data-dictionary by issuing analyze table statements. Secondly, the cost-based optimizer reads from left to right instead of right to left. So
from a,b,c
will have table c driving in rule-based approach and table a driving in cost-based approach.
Third, in general the cost-based optimizer produces incorrect results when small reference tables are included, that describes id's and so on. It has a preference to make those tables driving which is clearly silly.
Fourth, don't forget cost-based optimizer tries to reduce I/O. In some cases I found out that joins by means of nested-loops actually used more I/O compared to merge joins. You should use tkprof to collect actual I/O statistics before starting to curse Oracle. Fifth, As a last resort you can always correct the query-plan by introducing hints in your statement.

Sybrand Bakker
Senior IS Analyst
Bentley Systems Europe
Hoofddorp
The Netherlands

#include <std_dclaimer.h> Received on Thu Nov 23 1995 - 00:00:00 CET