SQL效率之where子句中的子查詢和函數

MorrisPresl 8年前發布 | 8K 次閱讀 SQL Oracle數據庫

工作中，曾有同事問我以下 sql 的效率如何，這里擴展一下這個問題并進行分析，主要說明 where 子句中的子查詢和函數執行次數及索引使用情況。

select * from trd_fundjour a

where oc_date = (select collect_date from hscon.sys_arg);

首先來看一下表的數據分布情況：

表 trd_fundjour 是分區表，里面是按月分區的，以oc_date為索引。

先看一下 201605 分區全掃描產生多少邏輯讀。

這里產生了 88 個一致讀，接下來執行前面的 sql 。

這里比前一次多出了 7 個一致讀，也多出了對 sys_arg 表的掃描，單獨查詢 sys_arg 表看看。

一致讀剛好是 7 個，從這里可以看出，子查詢只執行了一次，而不是針對分區中的每條記錄比較一次，因為這個分區中有 3279 條記錄。

下面來看能否用上索引，這里強制走索引看看。

顯然，在用 hint 強制的情況下索引是可以走的。就本例而言，走索引比全表掃描效率會更高，但在不用 hint 的情況下 Oracle 選的卻是全表掃描。雖然從表面上看是選擇了較差的執行計劃，其實是正常的，因為在生成執行計劃的時候 Oracle 得不到子查詢的值，無法判斷全表掃描和索引掃描哪個更優，因此選擇全表掃描就無可厚非了。所以，在可能的情況下應該將這些非相關子查詢執行結果賦給變量，把上面的 sql 拆成兩個語句來執行。

下面來看一下函數的表現， hscon.f_get_collectdate 的實現邏輯就是封裝前面 sql 中的子查詢，返回查詢結果。

將子查詢改成函數，可以看到遞歸調用和一致讀都大了很多，是哪里產生的呢？我們來單獨查一下函數看看。

從這里可以看出，函數調用一次就產生 1 個遞歸調用和 7 個一致讀。 3286*7 （函數執行一次 7 個一致讀）加上 88 （分區全掃描產生的一致讀）剛好是 23090 ，也就是說在前面的語句中函數執行了 3286 次！上文這一數字其實可以被拆分成 3279+7 兩部分來看， 3279 是分區的記錄數， 7 應該是和分區消除相關。我們可以得出這樣的結論：針對分區中的每一條記錄，函數都會調用一次進行比較。

既然前面的寫法會導致函數頻繁調用，我們修改一下 sql 寫法，先把函數生成一個結果集。