Oracle常见hint

Hint直接跟在select/update/merge/insert后，否则不起作用。结构：/*+ <hint> */

Parallel(4): 由于Oracle并发采用生产者-消费者模式，这里表示每组生产者/消费者有4个slave。实际进程数量有N*4+1个，通常N=2。 1是QC, Query Coordinator。

Append: 常见于insert语句，指令Oracle采用direct-path load。

Leading()： 指示Oracle在执行join(hash join, nested loop join, merge join)时的连接顺序。

select /*+ leading(t1) */

t1.ename, t2.dname
from emp t1, dept t2
where t1.deptno=t2.deptno

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |    18 (100)|          |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |    15 |   360 |    18   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMP     |    15 |   135 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    15 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

反之有：

select /*+ leading(t2) */
t1.ename, t2.dname
from emp t1, dept t2
where t1.deptno=t2.deptno;

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |   236 (100)|          |
|*  1 |  HASH JOIN         |      |    15 |   360 |  2640K|   236   (1)| 00:00:03 |
|   2 |   TABLE ACCESS FULL| DEPT |   100K|  1465K|       |   103   (1)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| EMP  |    15 |   135 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

请注意：因为连接顺序不一样，Oracle采用的连接方式也改变了。

在这里，也可以写成leading(t1,t2)或者leading(t2,t1)。

假如有多个互相矛盾的leading存在，Oracle将无视所有的leading。 Ordered Hint的优先级高于leading。

Ordered: 指示Oracle按照结果集在SQL中出线的顺序做连接。Oracle无法选择连接顺序。Oracle官方建议使用leading，而不是ordered。例子：

SELECT /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = 'Taylor'
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;

Merge(): 指示Oracle使用View Merge，前提是可以做View Merge，Oracle。如：

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
ORDER BY e1.last_name;

Result_Cache:  使用Query Result cache特性，将SQL结果集放入Shared pool的Result Cache中。注意SQL中的函数必须是deterministic的，比如sysdate就不是。

Index(): 可以写0,1,N个index。假如0个，Oracle考察每个可用索引的成本，选择最小的。假如N个，Oracle考察列出的每个索引的成本，选择最小的。0或N个时，Oracle也可能使用多个索引，再将结果集合并。1个时，Oracle只考虑该索引，也不会使用全表扫描。例子：

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

Full(): 使用全表扫描。例子：

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

Use_hash: 使用哈希连接。请看例子，在这里，连接顺序并不是l在前，h在后。Oracle会自动根据成本来选择连接顺序。需要Leading()来指定。该情况适用于use_nl和use_merge。

SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 2400;

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: 使用Merge sort join。