For example:
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;
For example:
SELECT /*+ USE_HASH(l h) */ * FROM orders h, order_items l WHERE l.order_id = h.order_id AND l.order_id > 2400;
For example:
SELECT /*+ USE_MERGE(employees departments) */ * FROM employees, departments WHERE employees.department_id = departments.department_id;
For example:
SELECT /*+ FULL(e) */ employee_id, last_name FROM hr.employees e WHERE last_name LIKE :b1;
For example:
SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id FROM employees WHERE department_id > 50;
For example:
SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;
驱动表的选择
SELECT /*+ LEADING(e j) */ * FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date;
当遇到子查询时,需要用qb_name命名子查询的查询语句块命名
For example:
SELECT /*+ leading(a@f) */ first_name FROM employees e where deptno in(select/*+ qb_name(a)*/ deptno from dept f)
两表是外连接,要该表hash连接的驱动表必须使用该hint
For example:
SELECT /*+ use_hash(e,a@f) swap_join_inputs(a@f) */ first_name FROM employees e where not exists (select/*+ qb_name(a)*/ 1 from dept f
where e.dept_no=f.dept_no)
原文:https://www.cnblogs.com/muzisanshi/p/12132002.html