RelationalDBDesign RelationalDBDesign

Optimizer Modes   «Prev  Next»
Lesson 4Overriding the default Optimizer Mode
ObjectiveOverride the default optimizer mode for a Query

Overriding default Optimizer Mode

As you saw, it is possible to reset the default optimizer mode for all queries. However, you may also override the optimizer mode on a query-by-query basis. There are two ways to override the default optimizer mode for any query, the use of the ALTER SESSION command and the use of SQL hints.

Using the ALTER SESSION command

If you want to override the default optimizer mode for any SQL that you execute (so long as it does not contain hints), issue the following command:
ALTER SESSION SET OPTIMIZER_GOAL=all_rows, first_rows, or rule
The snippet below shows how to do this in SQL*Plus:
SQL> alter session set optimizer_goal=rule;
Session altered.

Using SQL hints

While the ALTER SESSION command is very good for testing and development, a more surefire and permanent way of changing SQL is by using SQL hints. SQL hints are used to ensure that the access path for the SQL never changes. While there are many dozens of hints that can be added to SQL, these are the hints that change the optimizer mode for the entire statement:

/*+ all_rows */ 
/*+ first_rows */
/*+ rule */ 

Because Oracle SQL hints are embedded in comments, you will NEVER get an error message if the hint contains a syntax error. Hence, you must be very careful to run an EXPLAIN PLAN on all SQL after the hint has been added to ensure that it is doing what you intended.

Hints as insurance

Again, it is always a good idea to add a hint to “cement” the access path into every SQL statement after it has been tuned, even if the hint matches the default optimizer mode. This is great insurance against careless errors.
If another DBA mistakenly changes the default optimizer mode for your database, your query will still execute at optimal performance. Now let us conclude this module with a review.