Oracle 常用hint

表的连接方式

USE_NL Hint 

 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;

USE_HASH Hint

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;

USE_MERGE Hint

 

 For example:

SELECT /*+ USE_MERGE(employees departments) */ * 
  FROM employees, departments 
  WHERE employees.department_id = departments.department_id;

表的访问路径

FULL Hint 

 For example:

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM hr.employees e 
  WHERE last_name LIKE :b1;

INDEX Hint

For example:

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id
  FROM employees 
  WHERE department_id > 50;

INDEX_FFS Hint

 For example:

SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name
  FROM employees e;

驱动表的选择

LEADING Hint

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 Hint

当遇到子查询时，需要用qb_name命名子查询的查询语句块命名

 

For example:

SELECT /*+ leading(a@f) */ first_name
  FROM employees e where deptno in（select/*+ qb_name(a)*/ deptno from dept f）

SWAP_JOIN_INPUTS Hint

两表是外连接，要该表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）