一、提示（Hint）概述
1為什麼引入Hint?
Hint是Oracle資料庫中很有特色的一個功能，是很多DBA優化中經常采用的一個手段。那為什麼Oracle會考慮引入優化器呢？基于代價的優化器是很聰明的，在絕大多數情況下它會選擇正确的優化器，減輕DBA的負擔。
但有時它也聰明反被聰明誤，選擇了很差的執行計劃，使某個語句的執行變得奇慢無比。此時就需要DBA進行人為的幹預，告訴優化器使用指定的存取路徑或連接配接類型生成執行計劃，進而使語句高效地運作。Hint就是Oracle提供的一種機制，用來告訴優化器按照告訴它的方式生成執行計劃。
2不要過分依賴Hint
當遇到SQL執行計劃不好的情況，應優先考慮統計資訊等問題，而不是直接加Hint了事。如果統計資訊無誤，應該考慮實體結構是否合理，即沒有合适的索引。隻有在最後仍然不能SQL按優化的執行計劃執行時，才考慮Hint。
畢竟使用Hint，需要應用系統修改代碼，Hint隻能解決一條SQL的問題，并且由于資料分布的變化或其他原因(如索引更名)等，會導緻SQL再次出現性能問題。
3Hint的弊端
- Hint是比較"暴力"的一種解決方式，不是很優雅。需要開發人員手工修改代碼。
- Hint不會去适應新的變化。比如資料結構、資料規模發生了重大變化，但使用Hint的語句是感覺變化并産生更優的執行計劃。
- Hint随着資料庫版本的變化，可能會有一些差異、甚至廢棄的情況。此時，語句本身是無感覺的，必須人工測試并修正。
4Hint與注釋關系
提示是Oracle為了不破壞和其他資料庫引擎之間對SQL語句的相容性而提供的一種擴充功能。Oracle決定把提示作為一種特殊的注釋來添加。它的特殊性表現在提示必須緊跟着DELETE、INSERT、UPDATE或MERGE關鍵字。
換句話說，提示不能像普通注釋那樣在SQL語句中随處添加。且在注釋分隔符之後的第一個字元必須是加号。在後面的用法部分，會詳細說明。
5Hint功能
Hint提供的功能非常豐富，可以很靈活地調整語句的執行過程。通過Hint，我們可以調整：
- 優化器類型
- 優化器優化目标
- 資料讀取方式(通路路徑)
- 查詢轉換類型
- 表間關聯的順序
- 表間關聯的類型
- 并行特性
- 其他特性
二、Hint用法
1文法
1）關鍵字說明
- DELETE、INSERT、SELECT和UPDATE是辨別一個語句塊開始的關鍵字，包含提示的注釋隻能出現在這些關鍵字的後面，否則提示無效。
- "+"号表示該注釋是一個提示，該加号必須立即跟在"/*"的後面，中間不能有空格。
- hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。
- text是其它說明hint的注釋性文本
2）提示中的錯誤
提示中的文法錯誤不會報錯，如果解析器不能解析它，就會把它看做一個普通注釋處理。這也是容易造成困惑的一點，使用的Hint到底是否起效？可以采用一些手段，檢查提示的有效性。需要注意的是，那些文法正确但引用對象錯誤的提示是不會被報告的。
- explain plan + dbms_xplan
使用dbms_xplan輸出中的note選項。
- 10132事件
在10g中，這個事件産生的輸出文檔的末尾有一部分内容專門講提示。通過它可以檢查兩個方面:一是每個用到的提示都會被列出來。如果漏掉了哪個，就說明這個提示沒有被識别；二是檢查是否有一些資訊指明了出現提示錯誤(如果出錯，err值将大于0)。
3）提示中的對象
SELECT /*+ INDEX(table_name index_name) */ ...
- table_name是必須要寫的，且如果在查詢中使用了表的别名，在hint也要用表的别名來代替表名。
- index_name可以不必寫，Oracle會根據統計值選一個索引。
- 如果索引名或表名寫錯了，那這個hint就會被忽略。
- 如果指定對象是視圖，需要按此方法指定。/*+hint view.table ...*/，其中table是view中的表。
- 一個很常見的錯誤時，在使用提示的時候最易犯的錯誤是與表的别名有關。正确的規則是，當在提示中使用表時，隻要表有别名就應該使用别名而不是表名。
2提示的作用域
- 查詢塊
初始化參數提示對整個SQL語句起作用，其他的提示僅僅對查詢塊起作用。僅僅對單個查詢塊起作用的提示，必須在它控制的查詢塊内指定。
- 例外 - 全局提示
可以使用點号引用包含在其他查詢塊(假設這些塊已命名)中的對象。全局提示的文法可以支援兩層以上的引用，對象間必須用點号分隔。
- 命名查詢塊
既然where子句中的子查詢是沒有命名的，它們的對象就不能被全局提示引用。為了解決這個問題，10g中使用了另一種方法來解決-命名查詢塊。查詢優化器可以給每個查詢生成一個查詢塊名，而且還可以使用提示qb_name手工為每個查詢塊命名。大多數提示都可以通過參數來指定在那個查詢塊中有效。
*在提示中通過@來引用一個查詢塊。
3提示資料字典
Oracle在11g的版本中提供了一個資料字典—V$SQL_HINT。通過這個資料字典可以看到提示的出現版本、概要資料版本、SQL特性以及相反提示等。
- INVERSE
這個hint相反操作的hint。
- VERSION
代表着這個hint正式公布引入的版本。
三、Hint分類
1和優化器相關的
當對優化器為某個語句所制定的基本執行計劃不滿意時，最好的辦法就是通過提示來轉換優化器的模式，并觀察其轉換後的結果，看是否已經達到期望程度。如果隻通過轉換優化器的模式就可以獲得非常好的執行計劃，則就沒有必要額外使用更為複雜的提示了。
- OPT_PARAM
這個提示的作用就是使我們在某條語句中指定某個系統參數值。
- ALL_ROWS
為實作查詢語句整體最優化而引導優化器制定最少成本的執行計劃。這個提示會使優化器選擇一條可最快檢索所有查詢行的路徑，而代價就是在檢索一行資料時，速度很慢。
- FIRST_ROWS
為獲得最佳響應時間而引導優化器制定最少成本的執行計劃。這個提示會使優化器選擇可最快檢索出查詢的第一行(或指定行)資料的路徑，而代價就是檢索很多行時速度就會很慢。利用FIRST_ROWS來優化的行數，預設值為1，這個值介于10到1000之間，這個使用FIRST_ROWS(n)的新方法是完全基于代價的方法。它對n很敏感，如果n值很小，CBO就會生成包含嵌套循環以及索引查找的計劃；如果n很大，CBO會生成由哈希連接配接和全表掃描組成的計劃(類似ALL_ROWS)。
- CHOOSE
依據SQL中所使用到的表的統計資訊存在與否，來決定使用RBO還是CBO。在CHOOSE模式下，如果能夠參考表的統計資訊，則将按照ALL_ROWS方式執行。除非在查詢中的所有表都沒有經過分析，否則choose提示會對整個查詢使用基于代價的優化。如果在多表連接配接中有一個表經過分析過，那麼就會對整個查詢進行基于代價的優化。
- RULE
使用基于規則的優化器來實作最優化執行，即引導優化器根據優先順序規則來決定查詢條件中所使用到的索引或運算符的執行順序來制定執行計劃。這個提示強制oracle優先使用預定義的一組規則，而不是對資料進行統計；同時該提示還會使這個語句避免使用其他提示，除了DRIVING_SITE和ORDERED(不管是否進行基于規則的優化，這兩個提示都可使用)。
2和通路路徑相關的
- FULL
告訴優化器通過全表掃描方式通路資料。這個提示隻對所指定的表進行全表掃描，而不是查詢中的所有表。FULL提示可以改善性能。這主要是因為它改變了查詢中的驅動表，而不是因為全表掃描。在使用其他某些提示時，也必須使用FULL提示。隻有通路整個表時，才可利用CACHE提示将表進行緩存。并行組中的某些提示也必須使用全表掃描。
- CLUSTER
引導優化器通過掃描聚簇索引來從索引表中讀取資料。
- HASH
引導優化器按照哈希掃描的方式從表中讀取資料。
- INDEX
告訴優化器對指定表通過索引的方式通路資料。當通路資料會導緻結果集不完整時，優化器将忽略這個Hint。
- NO_INDEX
告訴優化器對指定表不允許使用索引。這個提示會禁止優化器使用指定索引。可以在删除不必要的索引之前在許多查詢中禁止索引。如果使用了NO_INDEX，但是沒有指定任何索引，則會執行全表掃描。如果對某個索引同時使用了NO_INDEX和會之産生沖突的提示(如INDEX)，這時兩個提示都會被忽略掉。
- INDEX_ASC
利用索引從表中讀取資料時，引導優化器對提示中所指定索引的索引列值按照升序使用範圍掃描。
- INDEX_COMBINE
告訴優化器強制選擇位圖索引。這個提示會使優化器合并表上的多個位圖索引，而不是選擇其中最好的索引(這是INDEX提示的用途)。還可以使用index_combine指定單個索引(對于指定位圖索引，該提示優先于INDEX提示)。對于B樹索引，可以使用AND_EQUAL提示而不是這個提示。
- INDEX_JOIN
索引關聯，當謂詞中引用的列上都有索引的時候，可以通過索引關聯的方式來通路資料。這個提示可以将同一個表的各個不同索引進行合并，這樣就隻需要通路這些索引就可以了，節省了回表查詢的時間。但隻能在基于代價的優化器中使用該提示。這個提示不僅允許隻通路表上的索引，這樣可以掃描更少的代碼塊，并且它比使用索引并通過rowid掃描整個表快5倍。
- INDEX_DESC
利用索引從表中讀取資料時，引導優化器對提示中所指定索引的索引列值按照降序使用範圍掃描。
- INDEX_FFS
告訴優化器以INDEX FFS(index fast full scan)的方式通路資料。INDEX_FFS提示會執行一次索引的快速全局掃描。這個提示隻通路索引，而不是對應的表。隻有查詢需要檢索的資訊都在索引上時，才使用這個提示。特别在表有很多列時，使用該提示可以極大地改善性能。
- INDEX_SS
強制使用index skip scan的方式通路索引。當在一個聯合索引中，某些謂詞條件并不在聯合索引的第一列時(或者謂詞并不在聯合索引的第一列時)，可以通過index skip scan來通路索引獲得資料。當聯合索引第一列的唯一值很少時，使用這種方式比全表掃描的方式效率要高。
3和查詢轉換相關的
- USE_CONCAT
将含有多個OR或者IN運算符所連接配接起來的查詢語句分解為多個單一查詢語句，并為每個單一查詢語句選擇最優化查詢路徑，然後再将這些最優化查詢路徑結合在一起，以實作整體查詢語句的最優化目的。隻有在驅動查詢條件中包含OR的時候，才可以使用該提示。
- NO_EXPAND
引導優化器不要為使用OR運算符号(或IN運算符)的條件制定互相結合的執行計劃。正好和USE_CONCAT相反。
- REWRITE
當表連接配接的對象是資料量比較大的表或者需要獲得使用統計函數處理過的結果時，為了提高執行速度可預先建立物化視圖。當使用者要求查詢某個查詢語句時，優化器會在從表中和從物化視圖中讀取資料的兩種方法中選擇一個更有效的方法來讀取資料。該執行方法稱之為查詢重寫。使用REWRITE提示引導優化器按照該方式執行。
- MERGE
為了能以最優方式從視圖或者嵌套視圖中讀取資料，通過變換查詢語句來直接讀取視圖使用的基表資料，該過程被稱之為視圖合并。不同的情況其具體使用類型也有所不同。該提示主要在視圖未發生合并時被使用。尤其是對比較複雜的視圖或者嵌套視圖(比如使用了GROUP BY或DISTINC的視圖)使用該提示，有時會取得非常好的效果。
- UNNEST
提示優化器将子查詢轉換為連接配接的方式。也就是引導優化器合并子查詢和主查詢并且将其向連接配接類型轉換。
- NO_UNNEST
引導優化器讓子查詢能夠獨立地執行完畢之後再跟外圍的查詢做FILTER。
- PUSH_PRED
使用該提示可以将視圖或嵌套視圖以外的查詢條件推入到視圖之内。
- NO_PUSH_PRED
使用該提示確定視圖或嵌套視圖以外的查詢條件不被推入到視圖内部。
- PUSH_SUBQ
使用該提示引導優化器為不能合并的子查詢制定執行計劃。不能合并的子查詢被優先執行之後，該子查詢的執行結果将扮演縮減主查詢資料查詢範圍的提供者角色。通常在無法執行子查詢合并的情況下，子查詢扮演的都是檢驗者角色，是以子查詢一般被放在最後執行。在無法被合并的子查詢擁有較少的結果行，或者該子查詢可以縮減主查詢查詢範圍的情況下，可以使用該提示引導優化器最大程度地将該子查詢放在前面執行，以提高執行速度。但如果子查詢執行的是遠端表或者排序合并連接配接的一部分連接配接結果，則該提示将不起任何作用。
- NO_PUSH_SUBQ
使用該提示将引導優化器将不能實作合并的子查詢放在最後執行。在子查詢無法縮減主查詢的查詢範圍，或者執行子查詢開銷較大的情況下，将這樣的子查詢放在最後執行可以在某種程度上提高整體的執行效率。也就是說，盡可能地使用其他查詢條件最大程度地縮減查詢範圍之後，再執行子查詢。
4和表連接配接順序相關的
這些提示可以調整表連接配接的順序。調整表連接配接的順序并不是隻能使用這些提示，在嵌套循環連接配接方式中也可以讓提示來引導優化器使用由驅動查詢條件所建立的索引。然而，該方法隻有在使用的索引和表連接配接順序同時被調整的情況下才比較有效。一般而言，這些提示主要在執行多表連接配接和表之間的連接配接順序比較混亂的情況下才使用，也在排序合并連接配接或哈希連接配接方式下，為引導優化器優先執行資料量比較少得表時使用。
- LEADING
在一個多表關聯的查詢中，這個Hint指定由哪個表作為驅動表，即告訴優化器首先要通路那個表上的資料。引導優化器使用LEADING指定的表作為表連接配接順序中的第一個表。該提示既與FROM中所描述的表的順序無關，也與作為調整表連接配接順序的ORDERED提示不同，并且在使用該提示時并不需要調整FROM中所描述的表的順序。當該提示與ORDERED提示同時使用時，該提示被忽略。
這個提示類似ORDERED提示，它允許指定驅動查詢的表，然後由優化器來判斷下一個要通路的表。如果使用這個提示指定多張表，那麼就可以忽略這個提示。
- ORDERED
引導優化器按照FROM中所描述的表的順序執行連接配接。如果和LEADING提示被一起使用，則LEADING提示将被忽略。由于ORDERED隻能調整表連接配接的順序并不能改變表連接配接的方式，是以為了改變表的連接配接方式，經常将USE_NL、USE_MERGE提示與ORDERED提示放在一起使用。
5和表連接配接操作相關的
- USE_NL
使用該提示引導優化器按照嵌套循環連接配接方式執行表連接配接。它隻是指出表連接配接的方式，對于表連接配接順序不會有任何影響。
- USE_MERGE
引導優化器按照排序合并連接配接方式執行連接配接。在有必要的情況下，推薦将該提示與ORDERED提示一起使用。提示通常用于獲得查詢的最佳吞吐量。假設将兩個表連接配接在一起，從每個表傳回的行集将被排序，然後再被合并(也就是合并排序)，進而組成最終的結果集。由于每個行先被排序之後才進行合并，是以在給定查詢中檢索所有行時，速度将會最快。如果需要以最快速度傳回第一行，就應該使用USE_NL提示。
- USE_HASH
該提示引導優化器按照哈希連接配接方式執行連接配接。在執行哈希連接配接時，如果由于某一邊的表比較小，進而可以在記憶體中實作哈希連接配接，那麼就能夠獲得非常好的執行速度。由于在大部分情況下優化器會通過對統計資訊的分析來決定Build Input和Prove Input，是以建議不要使用ORDERED提示随意改變表的連接配接順序。但是當優化器沒能做出正确判斷時，或者像從嵌套視圖中所獲得的結果集合那樣不具備統計資訊時，可以使用該提示。
6和并行相關的
- PARALLEL
指定SQL執行的并行度，這個值将會覆寫表自身設定的并行度。如果這個值為default，CBO使用系統參數。從表中讀取大量資料和執行DML操作時使用該提示來指定SQL的并行操作。一般情況下需要在該提示中指定将要使用的并行線程個數。如果在該提示中沒有指定并行度的個數，則優化器将使用PARALLEL_THREADS_PER_CPU參數所指定的值進行自動計算。如果在定義表時指定了PARALLEL，那麼在能夠使用并行操作的情況下，即使沒有使用該提示，優化器也會按照指定的并行級别選擇并行操作。但是如果想在DELETE、INSERT、UPDATE、MERGE等DML操作中使用并行操作，則必須要在會話中設定ALTER
SESSION ENABLE PARALLEL DML。在某個會話中所設定的并行級别也可以被引用在内部的GROUP BY或者排序操作中。在并行操作中如果出現了某個限制要素，則該提示将被忽略。
- NOPARALLEL/NO_PARALLEL
在SQL語句禁止使用并行。在有些版本中用NO_PARALLEL提示來代替NOPARALLEL提示。
- PQ_DISTRIBUTE
為了提高并行連接配接的執行速度，使用該提示來定義使用何種方法在主從程序之間(例如生産者程序和消費者程序)配置設定各連接配接表的資料行。
- PARALLEL_INDEX
為了按照并行操作的方式對分區索引進行索引範圍掃描而使用該提示，并且可以指定程序的個數。
7其他相關的
- APPEND
讓資料庫以直接加載的方式(direct load)将資料加載入庫。這個提示不會檢查目前是否有插入所需要的塊空間，相反它會直接将資料添加到新塊中。這樣會浪費空間，但可以提高插入的性能。需要注意的是，資料将被存儲在HWM之上的位置。
- APPEND_VALUES
在11.2中，Oracle新增了APPEND_VALUES提示，使得INSERT INTO VALUES語句也可以使用直接路徑插入。
- CACHE
在全表掃描之後，資料塊将留在LRU清單的最活躍端。如果設定表的CACHE屬性，它的作用和HINT一樣。這個提示會将全表掃描全部緩存到記憶體中。如果表很大，會占用大量記憶體。是以适用于使用者經常通路的較小的表。
- NOCACHE
引導優化器将通過全表掃描方式擷取的資料塊緩存在LRU清單的最後位置，這樣可以讓資料庫執行個體緩存中的這些資料塊被優先清除。這是優化器在Buffer Cache中管理資料塊的預設方法(僅針對全表掃描)。
- QB_NAME
使用該提示為查詢語句塊命名，在其他查詢語句塊可以直接使用該查詢語句塊的名稱。
- DRIVING_SITE
這個提示在分布式資料庫操作中有用。指定表是處理連接配接所在的位置。可以限制通過網絡處理的資訊量。此外，還可以建立遠端表的本地視圖來限制從遠端站點檢索的行。本地視圖應該有where子句，進而視圖可以在将行發送回本地資料庫之前限制從遠端資料庫傳回的行。
- DYNAMIC_SAMPLING
提示SQL執行時動态采樣的級别。這個級别為0~10，它将覆寫系統預設的動态采樣級别。等級越高，所獲得統計資訊的準确率越高。該提示的功能就是為了確定将動态采樣原理應用在單個SQL中。
- AND_EQUAL
這個提示會使優化器合并表上的多個索引，而不是選擇其中最好的索引(這是INDEX提示的用途)。這個提示與前面的INDEX_JOIN提示有差別，以此指定的合并索引随後需通路表，而INDEX_JOIN提示則隻需通路索引。如果發現需經常用到這個提示，可能需要删除這些單個索引而改用一個組合索引。需要查詢條件裡面包括所有索引列，然後取得每個索引中得到的rowid清單。然後對這些對象做merge join，過濾出相同的rowid後再去表中擷取資料或者直接從索引中獲得資料。在10g中，and_equal已經廢棄了，隻能通過hint才能生效。
- CARDINALITY
向優化器提供對某個查詢語句的整體或部分的預測基數值，并通過參考該基數值來為查詢語句制定執行計劃。如果在該提示中沒有指定表的名稱，則該基數值将被視為從該查詢語句所獲得的最終結果行數。
四、Hint使用示例
下面通過一個例子說明一下提示的使用及在什麼情況下提示會被忽略。
(1).建構表
(2).使用INDEX提示
*在某些情況下，如果CBO認為Hint會導緻錯誤結果，那麼Hint則會忽略。該例子中因為ID字段可能為空，而索引是儲存空值的，是以count(*)使用索引将導緻錯誤的結果，故而使用了全表掃描，忽略了Hint。
(3).使用INDEX提示（非空字段）
*ID字段不可為空，是以COUNT可用索引掃描的方式處理，Hint生效了。
轉載自：http://dbaplus.cn/blog-77-738-1.html