最近，遇到了一個關于ORA-01841的報錯，起初，認為這個錯誤處理起來應該不困難，但實際上折騰了很久，才最終找到問題原因，并解決掉，下面將本次解決和分析的過程用樣例來說明。

ORA-01841的錯誤提示是“(full) year must be between -4713 and +9999, and not be 0”，翻譯過來，大意是完整的年份值需在-4712到+9999之間，并且不得為0。出現這個錯誤，通常都是數據本身存在問題導致的，但本案例中，又不僅僅是數據的問題。

下面就來回顧一下問題處理的過程。為了簡化問題，方便理解，以下描述均是在事后構建的模擬環(huán)境中進行的：

執(zhí)行以下SQL時，發(fā)生了ora-01841的報錯：

我們可以看到，內層查詢的結果集中，并沒有不正常的數據。

到此時，想了許久，也做了各種測試，但均沒有找到問題原因。決定看一下執(zhí)行計劃：

從執(zhí)行計劃中看，CBO對該SQL做了自動改寫，將外層查詢的條件，推到了內層查詢。而且，從謂詞信息部分，我們可以看到SQL中的條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”在兩個過濾條件中，是位于靠前的位置。

也就是說，當數據庫對表中的數據做過濾時，是先用“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”來檢查。這樣，如果有某行數據的C1列中的值不正常，就會導致這樣的報錯。

我們來驗證一下：

未被CBO自動改寫的原始SQL，其內層查詢，會將不能正常轉換為日期的數據排除掉，然后在外層再去做TO_DATE的轉換。如果CBO按照這種方式來處理，就不會報錯了。

知道了原因，那我們要如何處理呢？
我們可以改寫SQL，使其必須先執(zhí)行內層查詢，然后再執(zhí)行外層查詢。
比如可以在內層查詢中加入ROWNUM。

或者，在內層查詢上，對C1進行一些不影響結果值的運算。例如：

如上所示，這種處理方法，雖然外層的過濾條件被推入到了內層，但會放到后邊執(zhí)行，這樣，當前邊的條件已經將不正常的數據過濾掉后，也就不會報錯了。
同理，對C1做一些UPPER，LOWER的函數運算，也有同樣的效果。

但是，這又引起了我的一個新的疑問，如果初始SQL就是只有一層（如下所示），兩個過濾條件在一起時，CBO是先用哪個過濾條件來過濾呢？

如上所示，看來和條件出現的順序是無關的。
但是，如果是RBO（基于規(guī)則的優(yōu)化器）模式，則會是先使用最后出現的條件，再使用前邊的。即，從后往前施加條件。這也是為什么網上曾流傳過的一個SQL編寫技巧–將過濾性最好的條件寫到WHERE子句中的最后。但，自Oracle 10g以后，默認就是CBO（基于成本的優(yōu)化器）了，除非像上面實驗那樣使用RULE的提示，否則，都會是以CBO方式來運作。

這正好給了我們一個啟示，在CBO下，在選擇先執(zhí)行哪個過濾條件時，是否會依據統(tǒng)計信息，計算并排序各個過濾條件的選擇性，選擇性越好的，則越會先被執(zhí)行呢？

我們測試驗證一下。主要測試思路如下：
1、默認情況下，CBO估算大部分非相等的過濾條件時，都會采用5%這樣一個選擇率。所以，條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”的選擇率會是5%，即，經過該條件過濾后，CBO認為會返回總記錄的5%的行數。

2、CBO在計算NOT LIKE這類條件時，其計算思路是先計算出LIKE的選擇率（類似于相等條件，是條件列中唯一值數量的倒數），然后用1-（like的選擇率）就是NOT LIKE的選擇率。

3、向表中再插入94行形如‘XXXXXXXXX1’這樣的記錄。構造一個有100行記錄的表，其中c1列上有100個唯一值，然后收集統(tǒng)計信息（注意，不要收集列上的直方圖信息，因為在有直方圖時，其計算邏輯和方法都要復雜得多，這里，我們只用列上的非直方圖的統(tǒng)計信息）。操作過程如下：

如上所示，對條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”返回行數的估算是5行。由于表中總共有100行，所以，選擇率是5/100=5%。與我們的理解是吻合的。

再來看對NOT LIKE的選擇率：

如上所示，我們看到對LIKE和NOT LIKE的估算，與我們的理解也是吻合的。
如果我們”先執(zhí)行過濾性好的條件“的猜測是正確的，那么這種情形下，顯然，條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”的過濾性（5%）要好過條件“c1 NOT like ‘X%’”的過濾性(99%)，所以，會先執(zhí)行前者。
我們來驗證一下：

那我們再來驗證一下，如果可以讓條件“c1 NOT like ‘X%’”的選擇率低于5%，那么我們就可能讓CBO選擇先執(zhí)行該條件了。即1-1/n<0.05，顯然，N要小于1.053，由于N表示的是唯一值的數量，所以，一定是個整數，即N只能是1了。

我們先來驗證一下前述兩個條件的選擇性是否如我們所愿，已經發(fā)生了改變：

如上所示，條件“to_date(c1,‘yyyy-mm-dd’) > date’2020-11-01’”的選擇率未變，仍然是5%，但條件“c1 NOT like ‘X%’”的選擇率已經低于5%，目前估算只有大約1行記錄滿足該條件。

那么我們再次執(zhí)行測試SQL，看看結果如何：

如上所示，這時，CBO已經先執(zhí)行條件“c1 NOT like ‘X%’”了。
同理，即使這時我們執(zhí)行最初的兩層SQL，其也應該是先執(zhí)行條件“c1 NOT like ‘X%’”。驗證一下：

果然是這樣。

附錄：提供上述模擬數據的生成腳本

墨天輪原文鏈接：https://www./db/42008（復制到瀏覽器中打開或者點擊“閱讀原文”立即查看）