19.1 Overview of Optimizer Hints

A hint is an instruction to the optimizer.

In a test or development environments, hints are useful for testing the performance of a specific access path.

19.1.2.3 Hints for Access Paths

FULL, CLUSTER, HASH, INDEX and NO_INDEX, INDEX_ASC and INDEX_DESC, INDEX_COMBINE and INDEX_JOIN

INDEX_JOIN, INDEX_FFS and NO_INDEX_FFS, INDEX_SS and NO_INDEX_SS, INDEX_SS and NO_INDEX_SS

19.1.2.4 Hints for Join Orders

LEADING, ORDERED

19.1.2.5 Hints for Join Operations

USE_NL and NO_USE_NL, USE_NL_WITH_INDEX, USE_MERGE and NO_USE_MERGE, USE_HASH and NO_USE_HASH, NO_USE_HASH

19.1.2.7 Hints for Parallel Execution

PARALLEL and NO_PARALLEL

PARALLEL_INDEX and NO_PARALLEL_INDEX

PQ_DISTRIBUTE

19.1.2.9 Additional Hints

APPEND, APPEND_VALUES, and NOAPPEND

CACHE and NOCACHE

PUSH_PRED and NO_PUSH_PRED

PUSH_SUBQ and NO_PUSH_SUBQ

QB_NAME

CURSOR_SHARING_EXACT

DRIVING_SITE

DYNAMIC_SAMPLING

MODEL_MIN_ANALYSIS

 

19.2 Specifying Hints

一个复杂的SQL语句, 你要给定全部的HINT, 比如你只给了单一的一个方面, 但是Oracle处于另一方面考虑, 有可能忽略你的hint

EX:

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)

USE_MERGE(j) FULL(j) */

e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal

FROM employees e1, employees e2, job_history j

WHERE e1.employee_id = e2.manager_id

AND e1.employee_id = j.employee_id

AND e1.hire_date = j.start_date

GROUP BY e1.first_name, e1.last_name, j.job_id

ORDER BY total_sal;