19 Using Optimizer Hints

 

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;