RULE Based Optimizer vs. COST Based Optimizer

RULE Based Optimizer

As its name implies the rule based optimizer uses a set of 15 rules to determine how to best process a query. The rule chosen is based strictly on query structure and pays no attention to the number of rows, partitions or other query specific features and statistics. The query optimization paths used by the cost based optimizer are:

Rank Path
1 Single row by ROWID
2 Single row by Cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite Index
9 Single Column index
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort-merge join
13 MAX or MIN on indexed column
14 ORDER BY indexed columns
15 Full table scan

The major benefit of the rule based optimizer was that for a specific query the execution plan was stable regardless of how big the table or its associated indexes grew as long as the index structure didn't change. The major disadvantages of the rule based optimizer are that the execution plan didn't change no matter how large the table or its indexes grew, and, it can't take advantage of new features as easily as a cost based optimizer.

COST Based Optimizer

The COST based optimizer was introduced in later versions of Oracle 7. The cost based optimizer uses a proprietary algorithm to assign a cost to each step of an execution plan for a query, and then, by looking at equivalent plans the optimizer can choose the one with the lowest cost for a specific query. The cost algorithm is dependent up on accurate table statistics in order to come up with accurate cost information. The table statistics are created and maintained by use of the ANALYZE command.

The major benefit of the cost based optimizer is that for changes in table and index sizes the optimizer can re-evaluate the execution plan and adjust to obtain an optimal performance profile. However, the cost based optimizer is only as good as the statistics fed to it, and, it tests a fully qualified DBA using the rule based optimizer can get better performance. However, unless you like to spend your free time using the tools tkprof, explain plan or third query party analyzers, I suggest jsut using the cost based optimizer and an "intelligent" table analyzer procedure.