聊聊oracle+hint 的使用

Oracle擁有非常好的優化算法，尤其是在8i版本之后引入CBO，很多的sql oracle都可以幫我們選擇非常好的執行計劃，但是有些時候oracle也會犯犯傻，會幫我們選擇非常不好的執行計劃，這個時候就需要我們來手工的優化優化，幫助oracle 提高下運行效率。

所謂hint，如翻譯成中文的意思一樣是示意、暗示，就是暗示數據庫應該怎么運行。

Hint的語法也很簡單，一般就是在select、update、insert后邊加一個注釋/*+hint*/。

例如有兩個表TABLE_A和TABLE_B，想讓這兩個表關聯的時候以hash_join、nest loop、merge join的方式進行關聯，就可以這樣寫：

Select /*+use_hash(a,b)*/ * from table_a a,table_b b where a.id=b.id;

Select /*+use_nl(a,b)*/ * from table_a a,table_b b where a.id=b.id;

Select /*+use_merge(a,b)*/ * from table_a a,table_b b where a.id=b.id;

使用hint的目的是為了提高oracle的執行效率。

談起執行效率，這里我們不得不說下索引，在一個業務訂單系統表中，一般會在這個表的時間字段上建立一個索引，當我們想要取某一天的數據的時候，可以直接以這個時間作為一個限制字段，系統正常來說會走索引取出這天的數據，但業務系統很多時候晚上是最忙的時候，這時候oracle中的收集表統計信息的功能大部分是被dba關掉的，所以這就很容易造成一種情況就是oracle根據表統計信息選擇執行計劃的時候，由于表統計信息過于老舊，oracle會選擇一個錯誤的執行計劃。

例如：TABLE_A和TABLE_B 關聯之前我們限制了TABLE_A的數據是昨天的數據，sql如下：

Select * from table_a a,table_b b wherea.id=b.id and a.gmt_create>=trunc(sysdate-1) and a.gmt_create

這段sql由于oracle統計信息的老舊可能沒有選擇table_a 表中gmt_create這個字段的索引(假設索引名字是index_gmt_create)，我們就可以用hint加以干預。Sql如下：

Select/*+index(a index_gmt_create)*/ * fromtable_a a,table_b b where a.id=b.id and a.gmt_create>=trunc(sysdate-1) anda.gmt_create

這個時候還發現其實tabe_a這個表經過時間過濾之后數據量變的已經很小，但是table_b還是很大，并且table_b的id字段建立的有索引的時候，便可以再加一個hint讓兩個表做nest loop關聯，sql如下：

Select/*+index(a index_gmt_create) use_nl(a,b)*/ * from table_a a,table_b bwhere a.id=b.id and a.gmt_create>=trunc(sysdate-1) and a.gmt_create

上面的例子加了兩個hint，從中可以看到兩個hint我們是用空格隔開的，所以當我們想要在一個sql語句中加多個hint的時候，以空格隔開就可以了。

有的時候，加一個hint可以把數據量變得很小，然而當我們要取的數據很大時，有可能占據了一個表數據的百分之八十，根據索引優化的可能性就沒有了。oracle還有一個hint 叫做parallel，中文名叫做并行，一般情況下數據庫執行的時候只會選擇服務器的一個cpu核心進行計算，但是我們的服務器一般情況下都是多核心的，全表計算的時候加一個/*+parallel（table_name n）*/(n是要使用的cpu核心數)很多時候比走索引還要快。

上面幾個列子都是對select語句加hint的優化，下面我們舉一個對insert語句優化的例子。

我們知道oracle執行語句的時候會伴隨著產生歸檔日志，這些日志是可以保證讀寫數據的數據一致性，幫助備份、容災……

有的時候我們也不一定要這些日志，因為這些日志會影響插入數據的速度。例如當我們想把table_a表中的數據插入到table_b表的時候，想要提高速度，怎么辦呢，hint可以幫忙，sql如下：

Insert/*+append*/ into table_b select *from table_a;

當然oracle的hint還有很多，今天只找出幾個比較有代表性的分享給了大家，希望可以幫助到大家。

本文作者：孫向東（點融黑幫），目前就職于點融網工程部data team，中國象棋，足球愛好者。