With Oracle 10G dropping the rules-based optimizer, there's a timely technical article on OTN about the Cost-Based Optimizer.
Up until now, the query optimizer in the Oracle server could either work out the optimum execution plan for a particular SQL statement by either using the original Rule-Based Optimizer, or by using the Cost-Based Optimizer which was introduced with Oracle 7.0. The Rule-Based optimizer took the SQL submitted by the user, and examined the syntax using a set of rules that, for instance, favoured using an index over a full table scan if an index was present. The cost-based optimizer uses statistics gathered on the underlying tables to calculate the total 'cost' of each potential execution plan, and eventually executes the plan which costs the least amount of processor and disk I/O usage.
In the first couple of releases of Oracle after 7.0, the cost-based optimizer wasn't generally very good, but nowadays it's almost always preferable to the rule-based optimizer, and with 10G, support for the rule-based optimizer will be dropped. One useful side-effect of dropping the rule-based optimizer is that it's no longer necessary to think about the order in which you put the table names in a join, or to use the 'ORDERED' hint to arrange the tables such that those producing a small number of rows are at the start of the join, as the CBO works this all out for you via the table statistics; and, with Oracle 9i onwards, you don't even have to generate the statistics in advance as you can alter the init.ora configuration file to generate statistics when needed on the fly.
10:43:53 AM 
|
|
