左外连接上的 Oracle 索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11419692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Indexes on Left Outer Joins
提问by denisb
So I'm having some issues with proper / any use of indexes in Oracle 11Gr2 and I'm trying to get a better understanding of how my explain plan ties back to my query so that I can apply indexing properly. When running the following query:
所以我在 Oracle 11Gr2 中正确/任何使用索引时遇到了一些问题，我试图更好地了解我的解释计划如何与我的查询相关联，以便我可以正确应用索引。运行以下查询时：
SELECT JLOG1.JLOG_KEY,
JLOG1.SRC_CD,
JLOG1.JRNL_AMT,
CASD.CONT_NO,
SUM (NVL (VJLOG.TDTL_AMT, 0)) TDTL_SUM
FROM GL_Journal_Logs JLOG1,
GL_JLOG_Details VJLOG,
CASE_DATA CASD
WHERE VJLOG.JLOG_KEY(+) = JLOG1.JLOG_KEY
AND CASD.CASE_KEY(+) = JLOG1.CASE_KEY
AND JLOG1.JRNL_CD = '0'
AND JLOG1.SRC_CD = '2'
AND JLOG1.ACCT_IF_CD = '0'
GROUP BY JLOG1.JLOG_KEY, JLOG1.SRC_CD,JLOG1.JRNL_AMT, CASD.CONT_NO
HAVING JLOG1.JRNL_AMT <> SUM (NVL (VJLOG.TDTL_AMT, 0));
I'm getting the following explain details: 
我得到以下解释细节：
I can understand that the indexes on my join "keys" (JLOG_KEY or CASE_KEY) wouldn't necessarily apply seeing as it's an outer join (or should they?), however when creating indexes on JLOG1 (JRNL_CD, SRC_CD, ACCT_IF_CD), technically would these take effect given my "where" clause?
我可以理解，我的连接“键”（JLOG_KEY 或 CASE_KEY）上的索引不一定适用，因为它是外部连接（或者应该是？），但是在 JLOG1（JRNL_CD、SRC_CD、ACCT_IF_CD）上创建索引时，技术上考虑到我的“where”条款，这些会生效吗？
Should I create any indexes at all given the circumstances or is there a better way of doing this?
鉴于情况，我应该创建任何索引还是有更好的方法来做到这一点？
采纳答案by spencer7593
Depending on the cardinality of the columns in your predicates, an appropriate index might be used on the GL_JLOG_DETAILS table, avoiding a full table scan. A covering index may avoid accessing the data pages at all:
根据谓词中列的基数，可能会在 GL_JLOG_DETAILS 表上使用适当的索引，从而避免全表扫描。覆盖索引可以完全避免访问数据页：
ON GL_JOURNAL_LOGS (JRNL_CD,SRC_CD,ACCT_IF_CD,JLOG_KEY,CASE_KEY,JRNL_AMT)
(You probably want the column with the most selective predicate first in that index)
（您可能希望该索引中最先具有选择性谓词的列）
Also, your query may be able to make effective use of indexes
此外，您的查询可能能够有效利用索引
ON GL_JLOG_DETAILS (JLOG_KEY, TDTL_AMT)
and
和
ON CASE_DATA (CASE_KEY, CONT_NO)
Also, be sure that the statistics on the tables and indexes are up-to-date.
此外，请确保表和索引的统计信息是最新的。
Also, that (+) notation for an OUTER JOIN may be limiting the optimizer.
Oracle now supports the ANSI style joins, which may allow the optimizer more latitude in coming up with an execution plan, e.g.
此外，OUTER JOIN 的 (+) 符号可能会限制优化器。
Oracle 现在支持 ANSI 样式的连接，这可能允许优化器在提出执行计划时有更大的自由度，例如
FROM GL_Journal_Logs JLOG1
LEFT
JOIN GL_JLOG_Details VJLOG ON VJLOG.JLOG_KEY = JLOG1.JLOG_KEY
LEFT
JOIN CASE_DATA CASD ON CASD.CASE_KEY = JLOG1.CASE_KEY
WHERE JLOG1.JRNL_CD = '0'
AND JLOG1.SRC_CD = '2'
AND JLOG1.ACCT_IF_CD = '0'
