/*+ ALL_ROWS */
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳吞吐量,使資源消耗最小化.
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id
FROM employees WHERE employee_id = 192;

/*+ APPEND */
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
insert /*+append */ into emp nologging

/*+ CACHE */
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區快取中最近最少列表LRU的最近使用端
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name FROM employees hr_emp;

/*+ CLUSTER */
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇物件有效.
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

/*+ CURSOR_SHARING_EXACT */
當cursor_sharing=similar或者force的時候使用比較方便
SELECT /*+cursor_sharing_exact*/ COUNT(*)FROM tt WHERE a = 2 AND b = 'kyp'
/*+ DRIVING_SITE */
是分散式查詢中另一個資料庫成為該查詢的驅動者
SELECT /*+ DRIVING_SITE(b) */ *
FROM employees a,b WHERE a.department_id = b.department_id;

/*+ DYNAMIC_SAMPLING */
SELECT /*+ dynamic_sampling(e 1) */ count(*) FROM employees e;

/*+ FACT */
the table specified in tablespec should be considered as a fact table
/*+ FIRST_ROWS */
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳響應時間,使資源消耗最小化.
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees WHERE department_id = 20;

/*+ FULL */
表明對錶選擇全域性掃描的方法.
SELECT /*+ FULL(e) */ employee_id, last_name FROM hr.employees e WHERE last_name LIKE :b1;

/*+ HASH */
hash scan to access the specified table. This hint applies only to tables stored in a table cluster.
/*+ INDEX */
表明對錶選擇索引的掃描方法.
SELECT /*+ INDEX (employees emp_department_ix)*/
employee_id, department_id FROM employees WHERE department_id > 50;

/*+ INDEX_ASC */
表明對錶選擇索引升序的掃描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';

/*+ INDEX_COMBINE */
為指定表選擇點陣圖訪問路經,如果INDEX_COMBINE中沒有提供作為引數的索引,將選擇出點陣圖索引的布林組合方式.
index_combine最早是用在bitmap index上的,在9i開始oracle預設可以使用在btree索引上,這是由_b_tree_bitmap_plans引數來控制的.oracle將btree索引中獲得的rowid資訊透過BITMAP CONVERSION FROM ROWIDS的步驟轉換成bitmap進行匹配,然後匹配完成後透過BITMAP CONVERSION TO ROWIDS再轉換出rowid獲得資料或者回表獲得資料.
SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
FROM employees e WHERE manager_id = 108 OR department_id = 110;

/*+ INDEX_DESC */
表明對錶選擇索引降序的掃描方法.
SELECT /*+ INDEX_DESC(e emp_name_ix) */ * FROM employees e;

/*+ INDEX_FFS */
對指定的表執行快速全索引掃描,而不是全表掃描的辦法.
SELECT /*+ INDEX_FFS(e emp_name_ix) */ first_name FROM employees e;
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';

/*+ INDEX_JOIN */
index_join這個hint的主要功能是透過對錶索引的hash_join操作獲得所需要的資料,從而避免回表執行查詢.針對源表資料較大,而返回結果資料都可以在索引中滿足的情況,這個hint比較有效，甚至可以為不帶查詢條件的檢索語句使用index_join的提示.只不過由index range scan變成了index fast full scan.
SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id FROM employees e
WHERE manager_id < 110 AND department_id < 50;

Execution Plan
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (34)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 3 | 21 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN| EMP_DEPARTMENT_IX | 3 | 21 | 2 (50)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| EMP_MANAGER_IX | 3 | 21 | 2 (50)| 00:00:01 |
----------------------------------------------------------------------------------------

/*+ INDEX_SS */
此hint明確地為指定表格選擇index skip scan。如果語句使用index range scan，Oracle將以對其索引值的升序排列來檢查索引入口。在被分割的索引中，其結果為對每個部分內部的升序排列。
SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name FROM employees e WHERE first_name = 'Steven';

/*+ INDEX_SS_ASC */
此hint明確地為指定表格選擇index skip scan。如果語句使用index range scan，Oracle將以對其索引值的升序排列來檢查索引入口。在被分割的索引中，其結果為對每個部分內部的升序排列。
SELECT * /*+ INDEX_SS_ASC(e) */FROM emp e WHERE ename = 'Jones';

/*+ INDEX_SS_DESC */
此hint明確為指定表格選擇index skip scan。如果語句使用index range scan，Oracle將以對其索引值的降序排列來檢查索引入口。在被分割的索引中，其結果為對每個部分內部的降序排列。
SELECT /*+ INDEX_SS_DESC(e emp_name_ix) */ last_name FROM employees e WHERE first_name = 'Steven';

/*+ LEADING */
將指定的表作為連線次序中的首表.
SELECT /*+ LEADING(e j) */ * FROM employees e, departments d, job_history j
WHERE e.department_id = d.department_idAND e.hire_date = j.start_date;
SELECT /*+ leading(t_max) use_hash(t_max t) */
*
FROM t_max,
t,
t_min
WHERE t.object_id = t_max.object_id
AND t_max.object_id = t_min.object_id;
/*+ MERGE */
能夠對檢視的各個查詢進行相應的合併.
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;

/*+ MODEL_MIN_ANALYSIS */
The MODEL_MIN_ANALYSIS hint instructs the optimizer to omit some compile-time optimizations of spreadsheet rules—primarily detailed dependency graph analysis. Other spreadsheet optimizations, such as creating filters to selectively populate spreadsheet access structures and limited rule pruning, are still used by the optimizer.

This hint reduces compilation time because spreadsheet analysis can be lengthy if the number of spreadsheet rules is more than several hundreds.
/*+ MONITOR */ --11g
/*+ NOAPPEND */
透過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;

/*+ NOCACHE */
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區快取中最近最少列表LRU的最近使用端
SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name FROM employees hr_emp;

/*+ NO_EXPAND */
對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於最佳化器對其進行擴充套件.
對於inlist查詢，Oracle通常會進行改寫，將形如select ..... from ....... where ....in (..........)的sql語句，改寫為union all的形式來執行，這個改寫通常是潛在的。
然而這一改寫可能存在問題，如果inlist中的值比較多的話，CBO花在分析執行路徑上的時間和成本都會相當大，此時我們通常需要阻止Oracle的這一展開操作.
我們可以透過NO_EXPAND提示來阻止Oracle進行這樣的改寫。
使用了NO_EXPAND提示後會使用"inlist iterator"方式來執行SQL，這樣可以用到index。
SELECT /*+ NO_EXPAND */ *
FROM employees e, departments d WHERE e.manager_id = 108 OR d.department_id = 110;

/*+ NO_FACT */
The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table.
/*+ NO_INDEX */
SELECT /*+ NO_INDEX(employees emp_empid) */ employee_id FROM employees WHERE employee_id > 200;

/*+ NO_INDEX_FFS */
此hint使CBO拒絕對指定表格的指定標籤進行fast full-index scan。
SELECT /*+ NO_INDEX_FFS(items item_order_ix) */ order_id FROM order_items items;

/*+ NO_INDEX_SS */
此hint使CBO拒絕對指定表格的指定標籤進行skip scan。
/*+ NO_INDEX_SS (tablespecindexspec ) */
/*+ NO_MERGE */
對於有可合併的檢視不再合併.
SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name
FROM employees e1,
(SELECT location_id, department_id, department_name
FROM departments WHERE location_id = 1700) seattle_dept
WHERE e1.department_id = seattle_dept.department_id;

/*+ NO_MONITOR */ --11g
/*+ NO_PARALLEL */
建議不執行並行查詢
SELECT /*+ NO_PARALLEL(hr_emp) */ last_name FROM employees hr_emp;

/*+ NO_PARALLEL_INDEX */
建議不併行化索引範圍掃描
SELECT * /*+ NO_PARALLEL_INDEX */
FROM emp
WHERE empno BETWEEN 1001 AND 2002;

/*+ NO_PUSH_PRED */

SELECT /*+ NO_MERGE(v) NO_PUSH_PRED(v) */ *
FROM employees e,(SELECT manager_id FROM employees ) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;

/*+ NO_PUSH_SUBQ */
在儘可能最晚的時間計運算元查詢
SELECT e.ename,
d.dname /*+ NO_PUSH_SUBQ */
FROM emp e,
(SELECT * FROM dept WHERE deptno = 10) d
WHERE e.deptno = d.deptno;

/*+ NO_PX_JOIN_FILTER */
阻止最佳化器使用並行聯合點陣圖過濾器
/*+ NO_QUERY_TRANSFORMATION */
SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
FROM (SELECT * FROM employees e) v WHERE v.last_name = 'Smith';

/*+ NO_RESULT_CACHE */ --11g
/*+ NO_REWRITE */
禁止對查詢塊的查詢重寫操作.
SELECT /*+ NO_REWRITE */ sum(s.amount_sold) AS dollars
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

/*+ NO_STAR_TRANSFORMATION */
此hint使CBO忽略star 詢問資訊。
/*+ NO_STAR_TRANSFORMATION */
/*+ NO_UNNEST */
關閉特定子查詢塊
SELECT /*+ NO_UNNEST */
COUNT(*)
FROM horses
WHERE horse_name LIKE 'M%'
AND horse_name NOT IN
(SELECT horse_name FROM horse_owners WHERE owner LIKE '%Lombardo%');

/*+ NO_USE_HASH */
此hint使CBO透過把指定表格作為內部表格的方式，拒絕hash joins把每個指定表格加入到另一原始行
SELECT /*+ NO_USE_HASH(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id;

/*+ NO_USE_MERGE */
此hint使CBO透過把指定表格作為內部表格的方式，拒絕sort-merge把每個指定表格加入到另一原始行。
SELECT /*+ NO_USE_MERGE(e d) */ *
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY d.department_id;

/*+ NO_USE_NL */
Hint no_use_nl使CBO執行迴圈巢狀，透過把指定表格作為內部表格，把每個指定表格連線到另一原始行。透過這一hint，只有hash join和sort-merge joins會為指定表格所考慮。
SELECT /*+ NO_USE_NL(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;
When this hint is specified, only hash join and

/*+ NO_XMLINDEX_REWRITE */ --11g
SELECT /*+NO_XMLINDEX_REWRITE*/ count(*) FROM table WHERE existsNode(OBJECT_VALUE, '/*') = 1;

/*+ NO_XML_QUERY_REWRITE */
The NO_XML_QUERY_REWRITE hint instructs the optimizer to prohibit the rewriting of XPath expressions in SQL statements
SELECT /*+NO_XML_QUERY_REWRITE*/ XMLQUERY('') FROM dual;

/*+ OPT_PARAM */ --11g
SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

/*+ ORDERED */
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連線.
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 = :b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;
select /*+ ordered use_hash(t_max t) */ * from t,t_max where t.object_id = t_max.object_id ;

/*+ PARALLEL */
指定並行度
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, 5) */ last_name FROM employees hr_emp;
使用初始化引數中的預設並行度
SELECT /*+ FULL(hr_emp) PARALLEL(hr_emp, DEFAULT) */ last_name FROM employees hr_emp;

/*+ PARALLEL_INDEX */
為分割槽索引指定並行索引掃描的數量
SELECT /*+ PARALLEL_INDEX(table1, index1, 3) */......

/*+ PQ_DISTRIBUTE */
選擇在一個PQ中分發某個表的方法(out/in 的取值範圍: HASH/NONE/BROADCAST/PARTITION)
HASH, HASH
BROADCAST, NONE
NONE, BROADCAST
PARTITION, NONE
NONE, PARTITION
NONE, NONE
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list FROM r,s WHERE r.c=s.c;
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list FROM r,s WHERE r.c=s.c;

/*+ PUSH_PRED */
SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
FROM employees e,(SELECT manager_id FROM employees ) v
WHERE e.manager_id = v.manager_id(+) AND e.employee_id = 100;

/*+ PUSH_SUBQ */
在儘可能早的時間計運算元查詢
SELECT e.ename,
d.dname /*+ NO_PUSH_SUBQ */
FROM emp e,
(SELECT * FROM dept WHERE deptno = 10) d
WHERE e.deptno = d.deptno;

/*+ PX_JOIN_FILTER */
This hint forces the optimizer to use parallel join bitmap filtering.
/*+ QB_NAME */
DELETE /*+ push_subq (@qb) full (@qb) index (x) */
FROM wl_client_sus x
WHERE 1 = 1
AND x.status = 'S'
AND ROWID IN (SELECT /*+ qb_name (qb) */
ROWID
FROM (SELECT ROWID,
rank() over(PARTITION BY client_no, watchlist_type, matched_name, watchlist, field_desc, status ORDER BY ROWID ASC) AS rnk
FROM wl_client_sus
WHERE status = 'S') s
WHERE rnk > 1);

SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name FROM employees e WHERE last_name = 'Smith';

/*+ RESULT_CACHE */ --11g
/*+ REWRITE */
可以將檢視作為引數.

/*+ STAR_TRANSFORMATION */
種語句被自動轉換的方式，一般是用在星形查詢當中，即一個事實表，多個維表的關聯。其間，也許會用到bitmap index,也許不會。其他的語句自動轉換方式還有: merge （涉及到sub query）, rewrite (有mv)。
SELECT /*+ STAR_TRANSFORMATION */ *
FROM sales s, times t, products p, channels c
WHERE s.time_id = t.time_id
AND s.prod_id = p.product_id
AND s.channel_id = c.channel_id
AND p.product_status = 'obsolete';

/*+ UNNEST */
告訴最佳化器將子查詢轉化為連線的方式
SELECT t1.id,
t1.object_name
FROM t1
WHERE object_name IN (SELECT /*+ UNNEST */
t2.table_name
FROM t2);

/*+ USE_CONCAT */
對查詢中的WHERE後面的OR條件進行轉換為UNION ALL的組合查詢.USE_CONCAT和NO_EXPAND成了互為"反函式"。
SELECT /*+ USE_CONCAT */ * FROM employees e WHERE manager_id = 108 OR department_id = 110;

/*+ USE_HASH */
將指定的表與其他行源透過雜湊連線方式連線起來.
SELECT /*+ USE_HASH(l h) */ *
FROM orders h, order_items l
WHERE l.order_id = h.order_id
AND l.order_id > 3500;

/*+ USE_MERGE */
將指定的表與其他行源透過合併排序連線方式連線起來.
SELECT /*+ USE_MERGE(employees departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;

/*+ 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_NL_WITH_INDEX */
這項hint使CBO透過巢狀迴圈把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內部表格:如果沒有指定標籤，CBO必須可以使用一些標籤，且這些標籤至少有一個作為索引鍵值加入判斷;反之，CBO必須能夠使用至少有一個作為索引鍵值加入判斷的標籤。
SELECT /*+ USE_NL_WITH_INDEX(l item_product_ix) */ *
FROM Orders h, Order_Items l
WHERE l.Order_Id = h.Order_Id
AND l.Order_Id > 3500;