【DB筆試面試573】在Oracle中，常用Hint有哪些？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，常用Hint有哪些？

♣

答案部分

Oracle的Hint是用來提示Oracle的優化器，用來選擇用戶期望的執行計劃。在許多情況下，Oracle默認的執行方式並不總是最優的，只不過由於平時操作的數據量比較小，所以，好的執行計劃與差的執行計劃所消耗的時間差異不大，用戶感覺不到而已。但對於書寫操作大數據量的SQL而言，其SQL的書寫則需要先了解一下執行計劃是否最優或滿足生產需要。通常當從開發環境遷移到生產環境下時，往往會出現此類情況。

例如：假設有一張客戶表，在客戶類別上有索引。如果想查找某一類別用戶，而該類別用戶佔總數的比例高達90%，那麼此時採用全表掃描方式將會比索引掃描方式快。如果不使用Hint，那麼Oracle很可能會選擇使用索引方式來執行。

使用Hint可以實現以下功能：

（1）改變SQL中的表的關聯順序。

（2）改變SQL中的表的關聯方式。

（3）實現並行方式執行DML、DDL以及SELECT語句。

（4）改變表的訪問路徑（數據讀取方式）。

（5）調整查詢轉換類型，重寫SQL。

（6）調整優化器優化目標。

（7）調整優化器類型。

Oracle推出了一個隱含參數「_OPTIMIZER_IGNORE_HINTS」，取值為TRUE或FALSE，缺省值是FALSE。Oracle可以通過將該隱含參數設置為TRUE，使得Oracle優化器忽略語句中所有的Hint。顯然，Oracle提供此參數的目的就是在不修改應用的前提下，忽略所有Hint，讓Oracle優化器自己來選擇執行路徑。

Hint的語法格式如下所示：

{SELECT | INSERT | UPDATE | DELETE | MERGE} /*+ <具體的Hint內容>*／    

關於Hint需要注意以下幾點：

l Hint中第一個星號（*）和加號（+）之間不能有空格。

l Hint中加號（+）和具體的Hint內容之間可以有空格，也可以沒有空格，但通常為了規範和區別於注釋建議加上空格。

l Hint中的具體內容可以是單個Hint，也可以是多個Hint的組合，如果是後者，那麼各個Hint間至少需要用1個空格來彼此分隔。

l Hint必須緊隨關鍵字SELECT、INSERT、UPDATE、DELETE或MERGE之後。

l 如果在目標SQL中使用了Hint，那麼就意味着自動啟用了CBO，即Oracle會以CBO來解析含Hint的目標SQL。但是對於RULE和DRIVING_SITE來說，它們可以在RBO下使用，而且不自動啟用CBO。

l Hint中指定具體對象時（比如指定表名或索引名），不能帶上該對象所在SCHEMA的名稱，即使該SQL文本中己經有對應的SCHEMA名稱。

l Hint中指定具體表名時，如果該表在對應SQL文本中有別名，那麼在Hint中應該使用該表的別名。

l 對於簡單的SQL語句一般只有一個查詢塊（Query Block），那麼在其上設置Hint其作用範圍就是該語句塊，而對於複雜的有多個查詢語句的SQL語句（例如查詢中用到了子查詢、內聯視圖、集合等操作時），各個Hint的作用域是不同的。Hint生效的範圍僅限於它本身所在的查詢塊，如果在Hint中不指定該Hint生效的查詢塊，那麼Oracle會默認認為它生效的範圍是指該Hint所處於的查詢塊。

l 由於各種原因導致Hint被Oracle忽略後，Oracle並不會給出任何提示或者警告，更不會報錯，目標SQL依然可以正常執行。導致Hint失效的原因通常有：

①　使用的Hint有語法或者拼寫錯誤。

②　使用的Hint是無效的（例如，在非等值連接中使用了USE_HASH）。

③　使用的Hint是自相矛盾的（例如，即指定了FULL又指定了INDEX_FFS），但Oracle只是將自相矛盾的Hint全部忽略掉，而組合Hint中的其它Hint依然有效。

④　使用的Hint受到了查詢轉換的干擾。

⑤　依據Hint執行的結果是錯誤的（例如在非空的索引列上計算行數）。

⑥　使用的Hint受到了保留關鍵字的干擾。Oracle在解析Hint的時候，從左到右進行，如果遇到一個詞是Oracle關鍵字或保留字，那麼Oracle將忽略這個詞以及之後的所有詞。如果遇到的一個詞既不是關鍵字也不是Hint，那麼就忽略該詞。如果遇到的詞是有效的Hint，那麼就會保留該Hint。Oracle的保留字或者關鍵字可以通過視圖V$RESERVED_WORDS來查詢。由此可以知道下面5條SQL語句中只有1和4中的APPEND提示是起作用的。

1. INSERT /*+ APPEND,PARALLEL(T1) */ INTO T1 SELECT * FROM T2;  2. INSERT /*+ PARALLEL(T1), APPEND */ INTO T1 SELECT * FROM T2;  3. INSERT /*+ THIS IS APPEND */ INTO T1 SELECT * FROM T2;  4. INSERT /*+ THIS APPEND */ INTO T1 SELECT * FROM T2;  5. INSERT /*+ NOLOGGING APPEND */ INTO T1 SELECT * FROM T2;  

因為「IS」是一個關鍵字，「,」（逗號）也是一個關鍵字，所以，上面的第2和第3條SQL，Oracle解析時，當遇到「,」和「IS」時，就忽略了後面的所有Hint。在第4條SQL中，THIS並不是一個關鍵字，所以，APPEND提示有效。為了避免這樣的情況發生，當在SQL中書寫Hint時，在/*+ */這種結構內只寫Hint，而不要寫逗號，或者是其它的注釋。如果要對SQL寫注釋，那麼可以在專門的注釋結構中書寫。

在SQL語句優化過程中，經常會用到Hint，通過在SQL語句中設置Hint從而影響SQL的執行計劃，通過V$SQL_HINT視圖可以查詢所有的Hint，下表是一些常用的Hint：

表 3-19 常用Hint介紹

建議對上表中每個Hint都做相關的實驗來深入學習，而本書不再詳述。最後需要說明一下有關NOLOGGING的錯誤使用情況。下面幾條SQL都是使用NOLOGGING時的錯誤用法：

INSERT INTO T1 NOLOGGING;  INSERT INTO T1 SELECT * FROM T2 NOLOGGING;  INSERT /*+ NOLOGGING */ INTO T1 VALUES ('0');  INSERT /*+ NOLOGGING */ INTO T1 SELECT * FROM T2;  DELETE /*+ NOLOGGING */ FROM T1;  UPDATE /*+ NOLOGGING */ T1 SET A='1';  

實際上，上述所有的SQL沒有一個能夠實現「不產生」日誌的數據更改操作。事實上，NOLOGGING並不是Oracle的一個有效的Hint，而是一個SQL關鍵字，通常用於DDL語句中。這裡NOLOGGING相當於給SELECT的表指定了一個別名為「NOLOGGING」。下面是NOLOGGING的一些正確用法：

CREATE TABLE T1 NOLOGGING AS SELECT * FROM T2;  CREATE INDEX T1_IDX ON T1(A) NOLOGGING;  ALTER INDEX T1_IDX REDUILD ONLINE NOLOGGING;  ALTER TABLE T1 NOLOGGING;  

若面試官問如何強制一個SQL語句使用索引，此時就可以回答使用Hint，/*+INDEX(TABLE INDEX_NAME)*/來完成。

& 說明：

有關Hint的更多內容可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2125011/、http://blog.itpub.net/26736162/viewspace-2125709/

本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。