筆記：Oracle CARDINALITY Hint 的使用時機

在 PL/SQL 開發中，有時候會需要將一些 ID 的清單或物件「臨時」找個位置來放，然後用來當 where 條件（用完就丟）。這時候就蠻有機會用自定義的集合物件（Collection Types），然後透過 TABLE() 函數轉型將這些變數傳入 SQL 的 WHERE IN 條件裡面。

這種實作雖然方便，但卻有可能會讓 Oracle 優化器誤判，導致執行計畫（Execution Plan）走到較糟的 plan，進而延伸成效能的不定時炸彈。

範例語法

SELECT t1.id, t1.name 
FROM student t1 
WHERE t1.id IN (SELECT /*+cardinality(d1 1000)*/ column_value FROM table(v_ids) d1);

可能的雷

* 在開發測試的時候一切正常，結果一上 production 的時候炸了。變成 long query 或是跑很久。
* 同樣的 SQL 語法，平常都沒有事，結果突然就變得很慢。
* 檢查 SQL Plan，莫名其妙變成 Full Table Scan or Hash Join，而不是 Index Unique Scan。

個人經驗

要用這種集合物件來當條件時，就需要先了解資料的分佈到底如何。

只有當很明確的知道這個集合物件的筆數一定是相對少的時候，才用這種集合物件 + cardinality hint 的實作才較適合。

畢竟現在 Oracle 的優化器已經是越做越好，理論上開發人員伸手去介入它的 sql plan 不是一個健康的行為。

加這個 hint 只是讓它能夠穩定一些，而不是當資料庫有被重啟或重新 parsing 一次時，這段 SQL 語法的 plan 就會飄來飄去，造成 production issue。