Data Centers

Oracle Tip: The end of rule-based optimization in Oracle 10g

With Oracle 10g, the rule-based optimizer will no longer be available. However, find out how you can use Oracle's Auto Gathering feature to help maintain statistics.

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 rule-based queries.

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:

dbms_stats.gather_schema_stats('SCOTT',options=>'GATHER AUTO');

In Oracle 10g, even this is automatic. Statistics are scheduled to be gathered during periods of low activity.

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 first_rows_1/10/100.

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.

Editor's Picks

Free Newsletters, In your Inbox