首页 > 数据库技术 > 详细

Oracle 常用hint

时间:2020-01-02 13:12:35      阅读:133      评论:0      收藏:0      [点我收藏+]

表的连接方式

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)



 

Oracle 常用hint

原文:https://www.cnblogs.com/muzisanshi/p/12132002.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!