Databases Reference
In-Depth Information
• Index range scan descending hints
• Fast full index scan hints
• Join hints, including index joins, nested loop joins, hash joins, sort merge joins,
Cartesian joins, and join order
• Other optimizer hints, including access paths, query transformations, and parallel
execution
Hints come with their own set of problems. A hint looks just like a comment, as shown
in this extremely simple SQL statement. Here, the hint forces the optimizer to use the
EMP_IDX index for the EMP table:
SELECT /*+ INDEX(EMP_IDX) */ LASTNAME, FIRSTNAME, PHONE FROM EMP
If a hint isn't in the right place in the SQL statement, if the hint keyword is misspelled,
or if you change the name of a data structure so that the hint no longer refers to an
existing structure, the hint will simply be ignored, just as a comment would be. Because
hints are embedded into SQL statements, repairing them can be quite frustrating and
time-consuming if they aren't working properly. In addition, if you add a hint to a SQL
statement to address a problem caused by a bug in the cost-based optimizer and the
cost-based optimizer is subsequently fixed, the SQL statement will still not use the cor‐
rected (and potentially improved) optimizer.
However, hints do have a place—for example, when a developer has a user-defined
datatype that suggests a particular type of access. The optimizer cannot anticipate the
effect of user-defined datatypes, but a hint can properly enable the appropriate retrieval
path.
For more details about when hints might be considered, see the sidebar
“Accepting the
Verdict of the Optimizer” on page 129
later in this chapter.
Specifying an Optimizer Mode
In the previous section, we mentioned two optimizer modes: ALL_ROWS and
FIRST_ROWS. Two other optimizer modes for Oracle versions prior to Oracle Database
10
g
were:
RULE
Forces the use of the rule-based optimizer
CHOOSE
Allowed Oracle to choose whether to use the cost-based optimizer or the rule-based
optimizer
With an optimizer mode of CHOOSE, which previously was the default setting, Oracle
would use the cost-based optimizer if any of the tables in the SQL statement had statistics
associated with them. The cost-based optimizer would make a statistical estimate for
