Oracle常見hint(leading)

hd_system發表於2016-11-09
Oracle

Hint直接跟在select/update/merge/insert後，否則不起作用。結構：/*+ */

Parallel(4): 由於併發採用生產者-消費者模式，這裡表示每組生產者/消費者有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)時的連線順序。

[sql] view plain copy
  1. select /*+ leading(t1) */  
[sql] view plain copy
  1. t1.ename, t2.dname  
  2. from emp t1, dept t2  
  3. where t1.deptno=t2.deptno  
[sql] view plain copy
  1. ----------------------------------------------------------------------------------------  
  2. | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
  3. ----------------------------------------------------------------------------------------  
  4. |   0 | SELECT STATEMENT             |         |       |       |    18 (100)|          |  
  5. |   1 |  NESTED LOOPS                |         |       |       |            |          |  
  6. |   2 |   NESTED LOOPS               |         |    15 |   360 |    18   (0)| 00:00:01 |  
  7. |   3 |    TABLE ACCESS FULL         | EMP     |    15 |   135 |     3   (0)| 00:00:01 |  
  8. |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|          |  
  9. |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    15 |     1   (0)| 00:00:01 |  
  10. ----------------------------------------------------------------------------------------  
反之有：
[sql] view plain copy
  1. select /*+ leading(t2) */  
  2. t1.ename, t2.dname  
  3. from emp t1, dept t2  
  4. where t1.deptno=t2.deptno;  

[sql] view plain copy
  1. -----------------------------------------------------------------------------------  
  2. | Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |  
  3. -----------------------------------------------------------------------------------  
  4. |   0 | SELECT STATEMENT   |      |       |       |       |   236 (100)|          |  
  5. |*  1 |  HASH JOIN         |      |    15 |   360 |  2640K|   236   (1)| 00:00:03 |  
  6. |   2 |   TABLE ACCESS FULL| DEPT |   100K|  1465K|       |   103   (1)| 00:00:02 |  
  7. |   3 |   TABLE ACCESS FULL| EMP  |    15 |   135 |       |     3   (0)| 00:00:01 |  
  8. -----------------------------------------------------------------------------------  
請注意：因為連線順序不一樣，Oracle採用的連線方式也改變了。

在這裡，也可以寫成leading(t1,t2)或者leading(t2,t1)。

假如有多個互相矛盾的leading存在，Oracle將無視所有的leading。 Ordered Hint的優先順序高於leading。

Ordered: 指示Oracle按照結果集在SQL中出線的順序做連線。Oracle無法選擇連線順序。Oracle官方建議使用leading，而不是ordered。例子：

[sql] view plain copy
  1. SELECT /*+ ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity  
  2. FROM customers c, order_items l, orders o  
  3. WHERE c.cust_last_name = 'Taylor'  
  4. AND o.customer_id = c.customer_id  
  5. AND o.order_id = l.order_id;  

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

[sql] view plain copy
  1. SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary  
  2. FROM employees e1,  
  3. (SELECT department_id, avg(salary) avg_salary  
  4. FROM employees e2  
  5. GROUP BY department_id) v  
  6. WHERE e1.department_id = v.department_id  
  7. AND e1.salary > v.avg_salary  
  8. 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只考慮該索引，也不會使用全表掃描。例子：

[sql] view plain copy
  1. SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id  
  2. FROM employees  
  3. WHERE department_id > 50;  
Full(): 使用全表掃描。例子：
[sql] view plain copy
  1. SELECT /*+ FULL(e) */ employee_id, last_name  
  2. FROM hr.employees e  
  3. WHERE last_name LIKE :b1;  

Use_hash: 使用雜湊連線。請看例子，在這裡，連線順序並不是l在前，h在後。Oracle會自動根據成本來選擇連線順序。需要Leading()來指定。該情況適用於use_nl和use_merge。
[sql] view plain copy
  1. SELECT /*+ USE_HASH(l h) */ *  
  2. FROM orders h, order_items l  
  3. WHERE l.order_id = h.order_id  
  4. AND l.order_id > 2400;  

Use_NL: 使用巢狀迴圈連線。

[sql] view plain copy
  1. SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity  
  2.   FROM orders h, order_items l  
  3.   WHERE l.order_id = h.order_id;  

Use_Merge: 使用Merge sort join。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29209863/viewspace-2128173/，如需轉載，請註明出處，否則將追究法律責任。

相關文章