Databases Reference
In-Depth Information
10,000 rows, LARGETAB, as shown in
Figure 4-4
. If the optimizer chose to read
SMALLTAB first, the Oracle Database would read the 10 rows and then read LARGE‐
TAB to find the matching rows for each of the 10 rows. If the optimizer chose to read
LARGETAB first, the database would read 10,000 rows from LARGETAB and then read
SMALLTAB 10,000 times to find the matching rows. Of course, the rows in SMALLTAB
would probably be cached, reducing the impact of each probe, but you could still see a
dramatic difference in performance.
Figure 4-4. The effect of optimization choices
Differences like this could occur with the rule-based optimizer as a result of the ordering
of the table names in the query. In the previous situation the rule-based optimizer re‐
turned the same results for the query, but it used widely varying amounts of resources
to retrieve those results.
Cost-Based Optimization
To improve the optimization of SQL statements, Oracle introduced the
cost-based op‐
timizer
in Oracle7. As the name implies, the cost-based optimizer does more than simply
look at a set of optimization rules; instead, it selects the execution path that requires the
least number of logical I/O operations. This approach avoids the problems discussed in
the previous section. The cost-based optimizer would know which table was bigger and
would select the right table to begin the query, regardless of the syntax of the SQL
statement.
