我有一个客户端程序正在执行一个针对将一个表外部连接到另一个表的视图的查询。性能很差，我一直在尝试通过添加正确的索引来调整它。有问题的查询实际上只使用第二个表，所以我一直在直接针对该表进行测试。
我发现（几个）索引可以很好地用于针对表的查询，但是当我将其切换为使用视图时，它们停止使用任何索引，而是对两个表进行了全面扫描。由于这些表很大（每个表 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
