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 corrected (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??? later in this chapter.
Query Optimization | 115
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
10g were:
RULE
Forces the use of the rule-based optimizer
CHOOSE
Allowed Oracle to choose whether to use the cost-based optimizer or the rulebased
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 have
statistics associated with them.
Pages:
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268