This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.
More than a decade ago, all Oracle databases
used what is now called rule-based optimization, which uses a fixed
strategy to optimize a query’s results.
For example, if a table had a unique index and
the columns were fully qualified, the table would become the
driving table for the rest of the query–regardless of the number
of rows in each table. In those days, IT consultants and support
pros made a living by using tricks like this:
SELECT EMP.*,DEPT.DNAME FROM EMP WHERE EMP.DEPTNO =
DEPT.DEPTNO + 0;
The + 0 (which is illogical) was used to
disable the rule to use the index on the DEPT.DEPTNO column because
it was involved in a function and to prefer the nonunique index on
EMP.DEPTNO. It looked ugly, but it worked in situations where an
application and the indexes couldn’t be tuned anymore.
The introduction of Oracle 7 included a new
optimization engine: the cost-based optimizer. Cost-based
optimization uses statistics stored with database objects to help
evaluate which set of indexes and joins to follow. As long as you
keep the statistics current, the optimizer would usually choose the
fastest path possible. There are also some comments called
optimizer hints, which developers could use as a last resort to
tune a cost-based query.
Until Oracle9i, the Oracle database had
both optimizers available. The database chose which optimizer to
use based on whether statistics were built for a given table or
index. If there were statistics, it used cost-based optimization;
if there were no statistics, it used rule-based optimization. You
could also select which optimizer to use for a single session with
an ALTER SESSION command:
ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;
With Oracle 10g, the rule-based optimizer will
no longer be available. All queries will be executed based on
cost-based optimization. As long as you keep statistics current,
cost-based optimization should always be more efficient than
To help maintain statistics, Oracle has an Auto
Gathering feature that can automatically reanalyze tables for
optimizer statistics. You can tell Oracle9i to automatically gather
statistics with the command:
In Oracle 10g, even this is automatic.
Statistics are scheduled to be gathered during periods of low
While waiting for the day when you need to
upgrade to Oracle 10g, you should consider testing and moving
current applications to cost-based optimization. Start to gather
statistics in a testing environment by testing application
performance and removing any rule-based “hacks.” Also, it’s worth
trying out the different cost-based optimization rules, such as
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.