FIRST_ROWS optimizes plans for
returning the first set of rows from a SQL statement. You can specify the number of
rows using this parameter. The optimizer mode tilts the evaluation of optimization
scores slightly and, in some cases, may result in a different execution plan.
Oracle also gives youa way to influence the decisions of the optimizer with a technique
called hints. A hint is nothing more than a comment with a specific format
inside a SQL statement. Hints can be categorized as follows:
??? Optimizer SQL hints for changing the query optimizer goal
??? Full table scan hints
??? Index unique scan hints
??? 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.
Pages:
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267