Oracle 9i 外連接異常行為分析：LEFT OUTER JOIN 結果不符預期

問題複現

創建測試表並插入數據：

CREATE TABLE temp_test1 (a NUMBER(10));
INSERT INTO temp_test1 VALUES (1);
INSERT INTO temp_test1 VALUES (2);
INSERT INTO temp_test1 VALUES (3);
INSERT INTO temp_test1 VALUES (4);
COMMIT;

CREATE TABLE temp_test2 (b NUMBER(10), c VARCHAR2(2));
INSERT INTO temp_test2 VALUES (1, 'a');
INSERT INTO temp_test2 VALUES (2, 'a');
INSERT INTO temp_test2 VALUES (3, 'a');
INSERT INTO temp_test2 VALUES (5, 'b');
COMMIT;

執行左外連接

SELECT a, b, c
FROM temp_test1 LEFT OUTER JOIN temp_test2 ON temp_test1.a = temp_test2.b;

預期結果 vs 實際結果

預期：左表 4 行全部保留，右表無匹配行時 b, c 為 NULL。

實際觀察到的異常行為：某些情況下（如未收集統計信息、特定優化器模式），Oracle 9i 可能錯誤地將左表無匹配行過濾掉，或返回重複行，導致結果集行數少於左表行數。

根因分析

1. 優化器 Bug (9.2.0.x)：早期 9i 版本存在外連接轉換為內連接的錯誤轉換，導致外表行丟失。
2. 統計信息缺失：未執行 ANALYZE 或 DBMS_STATS.GATHER_TABLE_STATS，優化器基於默認假設生成錯誤計劃。
3. WHERE 條件誤放：若在 WHERE 子句中對右表列加條件（如 AND c = 'a'），隱式轉為內連接。

解決方案與最佳實踐

1. 升級補丁集

應用 Oracle 9.2.0.8 或更高補丁集，修復已知外連接 Bug。

2. 收集統計信息

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'TEMP_TEST1');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'TEMP_TEST2');

3. 使用 ANSI SQL:1999 語法（推薦）

SELECT t1.a, t2.b, t2.c
FROM temp_test1 t1
LEFT JOIN temp_test2 t2 ON t1.a = t2.b;

ANSI 語法語義更清晰，優化器處理更可靠，避免 Oracle 舊式 (+) 語法的陷阱。

4. 正確放置過濾條件

右表過濾條件放在 ON 子句，而非 WHERE：

-- 正確：先外連接，再過濾右表
SELECT t1.a, t2.b, t2.c
FROM temp_test1 t1
LEFT JOIN temp_test2 t2 ON t1.a = t2.b AND t2.c = 'a';

-- 錯誤：WHERE 過濾右表列會將外連接轉為內連接
SELECT t1.a, t2.b, t2.c
FROM temp_test1 t1, temp_test2 t2
WHERE t1.a = t2.b(+) AND t2.c = 'a';

5. 強制使用 NESTED LOOP（應急）

SELECT /*+ ORDERED USE_NL(t2) */ t1.a, t2.b, t2.c
FROM temp_test1 t1
LEFT JOIN temp_test2 t2 ON t1.a = t2.b;

驗證腳本

-- 對比新舊語法結果
SELECT 'ANSI' AS syntax, COUNT(*) cnt FROM temp_test1 t1 LEFT JOIN temp_test2 t2 ON t1.a = t2.b
UNION ALL
SELECT 'OLD' AS syntax, COUNT(*) cnt FROM temp_test1 t1, temp_test2 t2 WHERE t1.a = t2.b(+);

若兩者 COUNT 不一致，說明存在 Bug 或統計信息問題。