外部聯接抑制索引使用？
我有一個客戶端程序正在執行一個針對將一個表外部連接到另一個表的視圖的查詢。性能很差，我一直在嘗試通過添加正確的索引來調整它。有問題的查詢實際上只使用第二個表，所以我一直在直接針對該表進行測試。
我發現（幾個）索引對錶的查詢效果很好，但是當我將其切換為使用視圖時，它們停止使用任何索引，而是對兩個表進行了全面掃描。由於這些表很大（每個表 2-3 百萬行），因此速度非常慢。
為了簡單地測試，我更改了查詢以繞過並將外部聯接合併到查詢本身中。這成功地重現了問題，但留下了為什麼外連接不使用索引的謎團。
這是表格，包含我在測試時添加的所有索引：
CREATE TABLE TEST_DATA (ID NUMBER(11,0) PRIMARY KEY, FORMATTED_RESULT VARCHAR2(255 BYTE), F_RESULT NUMBER, IDNUM NUMBER(11,0), IDNUM_DESCRIPTION VARCHAR2(128 BYTE), LAB_NUMBER NUMBER(11,0), SEQ_NUMBER NUMBER(11,0), ORDERNO NUMBER(11,0), SUPPL_FORMATTED_RESULT VARCHAR2(255 BYTE), SUPPL_IDNUM NUMBER(11,0), SUPPL_IDNUM_DESCRIPTION VARCHAR2(128 BYTE), SUPPL_UNIT VARCHAR2(16 BYTE) ) ; CREATE UNIQUE INDEX TEST_LN_SQN_ORDER ON TEST_DATA (LAB_NUMBER, SEQ_NUMBER, ORDERNO) ; CREATE INDEX TEST_LN_SQN ON TEST_DATA (LAB_NUMBER, SEQ_NUMBER) ; CREATE INDEX TD_CUIDD_CUFR ON TEST_DATA (UPPER(COALESCE(SUPPL_IDNUM_DESCRIPTION,IDNUM_DESCRIPTION)), UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))) ; CREATE INDEX TD_UFR_IDN ON TEST_DATA (UPPER(FORMATTED_RESULT), IDNUM) ; CREATE INDEX TD_UIDD_UFR ON TEST_DATA (UPPER(IDNUM_DESCRIPTION), UPPER(FORMATTED_RESULT)) ; CREATE INDEX TD_CUFR_CIDN_SN_LN ON TEST_DATA (UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM), SEQ_NUMBER, LAB_NUMBER) ; CREATE INDEX TD_SN_LN_CUFR_CIDN ON TEST_DATA (SEQ_NUMBER, LAB_NUMBER, UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM)) ; CREATE INDEX TD_CUFR_CIDN ON TEST_DATA (UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM)) ;這是另一個表（我們並沒有真正用於此查詢的表）
CREATE TABLE REQUEST_INFO (NUMBER(11,0) PRIMARY KEY, CHARGE_CODE VARCHAR2(32 BYTE), LAB_NUMBER NUMBER(11,0), SEQ_NUMBER NUMBER(11,0) ) ; CREATE INDEX RI_LN_SN ON REQUEST_INFO (LAB_NUMBER, SEQ_NUMBER) ; CREATE INDEX RI_SN_LN ON REQUEST_INFO (SEQ_NUMBER, LAB_NUMBER) ;因此，首先，這是直接針對單個表的查詢，它成功使用了其中一個索引。
-- GOOD, Uses index : TD_CUFR_CIDN_SN_LN select td.LAB_NUMBER from test_DATA td where UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376' and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 ;現在這是使用帶有內部連接的兩個表的查詢。這也使用了索引並且執行速度很快。
-- GOOD, Uses indexes : TD_CUFR_CIDN_SN_LN AND RI_SN_LN select TD.LAB_NUMBER from REQUEST_INFO RI JOIN TEST_DATA TD ON TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376' and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549這是與左外連接相同的查詢，因為它是在視圖中編寫的。這不使用任何索引並且執行非常緩慢。
-- BAD, does not use indexes select TD.LAB_NUMBER from REQUEST_INFO RI LEFT JOIN TEST_DATA TD ON TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376' and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 ;現在在任何人說之前：這個查詢實際上在邏輯上與前一個相同。這是因為 WHERE 子句對外部表 (TD) 中的列進行過濾，這有效/邏輯地將外部聯接轉換為內部聯接（這就是為什麼在 ON 子句與 WHERE 子句中是否出現條件很重要的原因）。
現在，為了增加怪異，我決定看看如果我讓從外到內的強制更加明確會發生什麼：
-- GOOD, Uses indexes : TD_CUFR_CIDN_SN_LN AND RI_SN_LN select TD.LAB_NUMBER from REQUEST_INFO RI LEFT JOIN TEST_DATA TD ON TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376' and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 and TD.LAB_NUMBER IS NOT NULL ;令人難以置信的是，這奏效了！
所以這裡的問題是，1) 為什麼 Oracle 不自己解決這個問題？
並且 2) 是否有一些設置或索引等我可以創建，讓 Oracle 正確地解決這個問題並使用索引？
其他注意事項：
- 該視圖被各種其他查詢和客戶端使用，所以我不能只將它更改為這個查詢的內部聯接。
- 客戶端正在生成查詢，因此很難/幾乎不可能使用古怪的特殊情況條件來更改查詢，例如：“將此視圖用於此數據，除非您只需要此表中的這些列，然後使用不同的查看“，或”當您需要這些列並且只需要該表中的這些列時，然後將“IS NOT NULL”添加到 WHERE 子句“
歡迎任何建議或見解。
更新： 我也剛剛在 Oracle 11g 上嘗試過，我在那裡得到了完全相同的結果。
根據請求，這裡是解釋計劃輸出，首先是好的版本，它使用索引：
Rows Plan COST Predicates 3 SELECT STATEMENT 8 3 HASH JOIN 8 Access:TD.LAB_NUMBER=RI.LAB_NUMBER AND TD.SEQ_NUMBER=RI.SEQ_NUMBER 3 NESTED LOOPS 8 STATISTICS COLLECTOR 3 INDEX RANGE SCAN TD_CUFR_CIDN_SN_LN 4 Access:UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376' AND COALESCE(SUPPL_IDNUM,IDNUM)=40549, Filter:TD.LAB_NUMBER IS NOT NULL 1 INDEX RANGE SCAN RI_SN_LN 2 Access:TD.SEQ_NUMBER=RI.SEQ_NUMBER AND TD.LAB_NUMBER=RI.LAB_NUMBER 1 INDEX FAST FULL SCAN RI_SN_LN 2現在是壞版本：
Rows Plan COST Predicates 31939030 SELECT STATEMENT 910972 FILTER Filter:UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376' AND COALESCE(SUPPL_IDNUM,IDNUM)=40549 31939030 HASH JOIN OUTER 910972 Access:TD.LAB_NUMBER(+)=RI.LAB_NUMBER AND TD.SEQ_NUMBER(+)=RI.SEQ_NUMBER 6213479 TABLE ACCESS FULL REQUEST_INFO 58276 56276228 TABLE ACCESS FULL TEST_DATA 409612
這主要是對第 1 部分的部分回答，並帶有一些猜測。你我都知道以下查詢：
select TD.LAB_NUMBER from REQUEST_INFO RI LEFT JOIN TEST_DATA TD ON TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376' and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;相當於這個查詢：
select TD.LAB_NUMBER from REQUEST_INFO RI INNER JOIN TEST_DATA TD ON TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER AND UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376' and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;但是，這並不意味著 Oracle 知道這兩個查詢是等價的。Oracle 需要兩個查詢等價才能使用
TD_CUFR_CIDN_SN_LN索引。我們在這里希望的是OUTER JOIN轉換INNER JOIN。我沒有很幸運地找到關於這個的好資訊，所以讓我們看看解釋計劃：
添加
TD.LAB_NUMBER IS NOT NULL到WHERE子句是讓 Oracle 知道可以OUTER JOIN進行INNER JOIN轉換的一種非常直接的方法。通過查看突出顯示的行，我們可以看到它發生了。我認為幾乎任何列都允許轉換，儘管選擇錯誤的列可能會改變查詢結果。如果我們嘗試稍微複雜一些的過濾器，例如
(TD.LAB_NUMBER IS NOT NULL OR TD.SEQ_NUMBER IS NOT NULL)連接轉換就不會發生：
我們可以推斷出這
OUTER JOIN確實是一個，INNER JOIN但查詢優化器可能沒有被程式來做到這一點。在原始查詢中，您的COALESCE()表達式可能過於復雜，查詢優化器無法應用查詢轉換。這是一些範例的數據庫小提琴。
對於第二個問題，我想不出辦法解決這個問題。您可以嘗試利用表消除。正如您所說，此查詢甚至不需要該
REQUEST_INFO表。但是，有一些限制：目前有一些表格消除的限制：
- 不支持多列主鍵-外鍵約束。
- 在查詢的其他地方引用連接鍵將阻止表消除。對於內部連接，連接兩側的連接鍵是等效的，但如果查詢包含對錶中連接鍵的其他引用，否則這些引用可能會被消除，這會阻止消除。一種解決方法是重寫查詢以引用另一個表中的連接鍵（我們意識到這並不總是可能的）。
也許有一種方法可以解決這個問題，但我無法解決這些限制。
