Databases Reference
In-Depth Information
the tables that lacked statistics. In the years since the cost-based optimizer was intro‐
duced, the value of using costs has proven itself as this version of the optimizer has
grown in accuracy and sophistication, as the next section and the remainder of this
chapter illustrate.
Newer database releases and the cost-based optimizer
The cost-based optimizer makes decisions with a wider range of knowledge about the
data structures in the database than the previous rule-based optimizer. Although the
cost-based optimizer isn't flawless in its decision-making process, it does make more
accurate decisions based on its wider base of information, especially because it has
matured since its introduction in Oracle7 and has improved with each new release.
The cost-based optimizer also takes into account improvements and new features in the
Oracle Database as they are released. For instance, the cost-based optimizer understands
the impact that partitioned tables have on the selection of an execution plan, while the
rule-based optimizer does not. The cost-based optimizer optimizes execution plans for
star schema queries, heavily used in data warehousing, while the rule-based optimizer
has not been enhanced to deal effectively with these types of queries or leverage many
other such business intelligence query features.
Oracle Corporation was quite frank about its intention to make the cost-based optimizer
the
optimizer for the Oracle Database through a period of years when both optimizer
types were supported. In fact, since Oracle Database 10
g
, the rule-based optimizer is no
longer supported.
We will remind you of one fact of database design at this point. As good as the cost-
based optimizer is today, it is not a magic potion that remedies problems brought on
by a poor database and application design or a badly selected hardware and storage
platform. When performance problems occur today, they are most often due to bad
design and deployment choices.
Accepting the Verdict of the Optimizer
Some of you may doubt the effectiveness of Oracle query optimization if you are on an
old Oracle Database release prior to Oracle Database 10
g
. You may have seen cases in
which the query optimizer chose an incorrect execution path that resulted in poor per‐
formance. You may feel that you have a better understanding of the structure and use
of the database than the query optimizer. For these reasons, you might look to hints to
force the acceptance of the execution path you feel is correct.
We recommend using the query optimizer for all of your queries rather than using hints.
Although the Oracle developers who wrote the query optimizer had no knowledge of
your particular database, they did depend on a lot of customer feedback, experience,
and knowledge of how Oracle processes queries during the creation of the query opti‐
mizer. They designed the cost-based optimizer to efficiently execute all types of queries
