MOS文章實驗：ORA-01722 from Queries with Dependent Predicates

今天讀了一篇MOS文章，《ORA-01722, ORA-01839, ORA-01841, ORA-01847 or ORA-01858 from Queries with Dependent Predicates (文檔 ID 232243.1)》，整篇文章的目的就是為了闡述對于包含互相依賴關系謂詞的SQL語句産生錯誤的可能原因(To explain the possible causes of these errors in SQL statements that include predicates that are dependent on each other)。

文章指出可能的錯誤類型包括以下幾種：  

如果應用程式設計中需要對不同類型的資料做比較，但又沒有顯示轉換，那麼Oracle自己會根據一些規則做必要的類型轉換。當使用松散類型('loose typing')字段，且包含可變謂詞順序的場景下，在類型轉換發生之前如果不能删除會産生錯誤的列值，那麼就有可能産生上述的一些類型轉換錯誤。

除了修改應用程式，能否解決也取決于查詢語句。Oracle提供了/*+ ordered_predicates */這個HINT可以作為workaround，但前提是需要以要求的解析順序來改寫查詢語句。

還有一種更複雜的場景，就是使用視圖。CBO可以建立滿足查詢條件的最優執行計劃。這就意味着通常會将視圖和主查詢合并，我們也不能控制謂詞的解析順序。文章舉了一個示例，如下SQL查詢：

他會變成如下等價的形式：

如果視圖或内聯視圖使用/*+ no_merge */這個HINT，那麼就可以防止視圖被重寫(合并)。另一個可以阻止因視圖合并導緻錯誤的方法就是增加一個'不相關'的rownum謂詞(例如rownum > 0)，也會防止視圖合并。不能合并的視圖就不會允許謂詞和主查詢的謂詞合并使用，也就避免了錯誤的産生。當然，未來的版本可能會察覺到并删除這樣'不相關'的謂詞。

正常。資料類型和資料列值都是VARCHAR字元串，不需要類型轉換。

實驗語句1：

這裡需要将VARCHAR類型的字段轉換為NUMBER類型，然後和一個數字類型的值進行比較。對于data列中22這個記錄，包含NUMBER數字類型，是以轉換是有效的，但對于其他行，這種轉換就是無效的，因為不包含等價的數值，例如'Pet Foods Inc'。如果謂詞比較是對包含非數字類型的行，此時需要非數字類型值和數字類型值進行比較，在做類型轉換的時候就會報錯。如果謂詞比較從'data_type'列開始，删除所有包含非數字類型的行，那麼就不會産生錯誤。

實驗語句2：

如下SQL，如果首先解析内聯視圖，所有data列包含非數字類型值的行都會被過濾。

先解析子查詢，用data_type='zip'過濾後，結果集的行data列都是數字型，是以to_number()可以正常執行。

9i下我們看看優化器的模式是RBO：

如果此時收集表的統計資訊，那麼會影響執行計劃的選擇。

或者将session優化器模式改為CBO：

得到以下相同的結論。(原因：因為RBO下如果表存在統計資訊，則會采用CBO)

會先使用data_type='zip'過濾，然後再執行to_number()。

實驗語句4：

CBO下或者收集表的統計資訊後，都會先執行to_number()，再使用data_type='zip'，是以報錯。

實驗語句5：

實驗語句6：

但當該表收集統計資訊後，實驗語句6就會報錯：

即使使用/*+ no_merge */也如此。

總結：

1. 9i下，優化器預設模式是RBO，如果表沒有統計資訊，隻會按照RBO方式，SQL查詢語句按照謂詞從右側至左側的順序解析，如實驗語句1。

2. @dbsnake的書中曾介紹過，Oracle會内置一些查詢轉換規則，隻要目标SQL滿足了這些規則的要求，Oracle就會對其執行查詢轉換。Oracle 9i中查詢轉換是獨立于優化器的，和優化器類型無關，因為Oracle此時認為經過查詢轉換後的等價改寫SQL的執行效率一定比原目标SQL的執行效率高。我猜9i内置的查詢轉換規則，會先對子查詢内聯視圖做解析，或者子查詢展開後條件為where to_number(data) = 22 and data_type='zip';，如實驗語句2，但僅僅是猜測。

3. 9i下，如果表有統計資訊，或者alter session設定session級優化器模式為CBO，如MOS中提到的“The CBO's function is to generate execution plans that satisfy the query in the most optimal way it can.”，實驗語句3指出CBO下，對謂詞順序進行了調整，先使用data_type='zip'過濾，然後再執行to_number()。

4. 9i下的CBO，如實驗語句4，MOS中提到“That means views often get merged into the main query and so the order in which predicates are evaluated is not under your control”，從現象看先解析to_number()，導緻報錯。猜測做了子查詢展開，謂詞條件是where data_type='zip' and to_number(data) = 22，但從10053中，沒有看到原因。

5. 11g，優化器預設模式是CBO，會對謂詞順序進行調整，和9i下CBO的效果相同，如實驗語句5。

6. 同樣，沒有收集表統計資訊的前提下，實驗語句6可正常執行。

7. @dbsnake提過Oracle 10g及其以後的版本中，Oracle會對某些類型的查詢轉換計算成本，隻有當等價改寫SQL的成本值小于未經過查詢轉換的原始SQL的成本值時，Oracle才會對目标SQL執行這些查詢轉換。收集表統計資訊後，實驗語句5依舊可以正常執行，會按照先解析data_type='zip'，後解析to_number()的方式進行，不會報錯。但實驗語句6再次執行後出現錯誤，發現謂詞條件變為先解析to_number()，是以報錯，猜測收集統計資訊後，實驗語句6的執行成本發生了變化，導緻前後使用了不同的執行路徑，收集統計資訊後，實驗語句6成本值低的執行路徑，因為謂詞先解析了to_number()，導緻報錯。但從10053上未看出端倪。

雖然上述還有一些猜疑，暫時無從考證，但至少從這篇文章中學習了以下内容：

1. 不同版本RBO和CBO的使用，以及對執行SQL的影響。

2. explain plan for觀察查詢語句謂詞順序的變化。

3. 10053檢視執行計劃的成本選擇。