1 优化方法和目标Hints 2 访问路径Hints 3 查询转换Hints

4 连接顺序Hints 5 连接操作Hints 6 并行执行Hints

7 其他Hints

优化提示的分类

1 优化方法和目标Hints

2 访问路径Hints

3 查询转换Hints

4 连接顺序Hints

5 连接操作Hints

6 并行执行Hints

7 其他Hints

1 优化方法和目标Hints

ALL_ROWS

FIRST_ROWS(n)

CHOOSE

RULE

2 访问路径Hints

FULL

ROWID

SELECT /*+ROWID(employees)*/ *

FROM employees

WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

HASH

INDEX

INDEX_ASC

INDEX_COMBINE

SELECT /*+INDEX_COMBINE(employees salary_bmi hire_date_bmi)*/ *

FROM employees

WHERE salary < 50000 AND hire_date < '01-JAN-1990';

INDEX_JOIN

SELECT /*+index_join(employees emp_emp_id_pk emp_department_ix)*/

employee_id, department_id

FROM employees

WHERE department_id > 50;

INDEX_DESC

INDEX_FFS

NO_INDEX

AND_EQUAL

CLUSTER

SELECT /*+ CLUSTER */

employees.last_name, department_id

FROM employees, departments

WHERE department_id = 10

AND employees.department_id = departments.department_id;

3 查询转换Hints

USE_CONCAT

SELECT /*+USE_CONCAT*/ *

FROM employees

WHERE employee_id > 50 OR salary < 50000;

NO_EXPAND

SELECT /*+NO_EXPAND*/ *

FROM employees

WHERE employee_id = 50 OR employee_id = 100;

REWRITE

EXPAND_GSET_TO_UNION

NOREWRITE

MERGE

SELECT /*+MERGE(v)*/ e1.last_name, e1.salary, v.avg_salary

FROM employees e1,

(SELECT department_id, avg(salary) avg_salary

FROM employees e2

GROUP BY department_id) v

WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;

NO_MERGE

SELECT /*+NO_MERGE(dallas_dept)*/ e1.last_name, dallas_dept.dname

FROM employees e1,

(SELECT department_id, dname

FROM departments

WHERE loc = 'DALLAS') dallas_dept

WHERE e1.department_id = dallas_dept.department_id;

STAR_TRANSFORMATION

FACT

NO_FACT

4 连接顺序Hints

ORDERED

STAR

5 连接操作Hints

USE_NL

SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity

FROM orders h ,order_items l

WHERE l.order_id = h.order_id;

USE_MERGE

SELECT /*+USE_MERGE(employees departments)*/ *

FROM employees, departments

WHERE employees.department_id = departments.department_id;

USE_HASH

SELECT /*+USE_HASH(l l2) */ l.order_date, l.order_id, l2.product_id,

SUM(l2.unit_price*quantity)

FROM orders l, order_items l2

WHERE l.order_id = l2.order_id

GROUP BY l2.product_id, l.order_date, l.order_id;

DRIVING_SITE

SELECT /*+DRIVING_SITE(departments)*/ *

FROM employees, departments@rsite

WHERE employees.department_id = departments.department_id;

LEADING

HASH_AJ, MERGE_AJ, and NL_AJ

HASH_SJ, MERGE_SJ, and NL_SJ

SELECT * FROM departments

WHERE exists (SELECT /*+HASH_SJ*/ *

FROM employees

WHERE employees.department_id = departments.department_id

AND salary > 200000);

6 并行执行Hints

PARALLEL

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name

FROM hr.employees hr_emp;

SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT,DEFAULT) */ last_name

FROM hr.employees hr_emp;

NOPARALLEL

PQ_DISTRIBUTE

SELECT column_list /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/

FROM r,s

WHERE r.c=s.c;

PARALLEL_INDEX

SELECT /*+ PARALLEL_INDEX(table1, index1, 3, 2) +/

NOPARALLEL_INDEX

7 其他Hints

APPEND

NOAPPEND

CACHE

SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name

FROM hr.employees hr_emp;

NOCACHE

UNNEST

NO_UNNEST

PUSH_PRED

SELECT /*+ PUSH_PRED(v) */ t1.x, v.y

FROM t1

(SELECT t2.x, t3.y

FROM t2, t3

WHERE t2.x = t3.x) v

WHERE t1.x = v.x and t1.y = 1;

NO_PUSH_PRED

PUSH_SUBQ

NO_PUSH_SUBQ

ORDERED_PREDICATES

CURSOR_SHARING_EXACT

DYNAMIC_SAMPLING

SELECT /*+ dynamic_sampling(1) */ *

FROM ...