Oracle 加入时不使用索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17666331/
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 not using index when joining
提问by Lock
I am very new to index and explain plans, so please bear with me! I am trying to tune a query but I am having issues.
我对索引和解释计划很陌生，所以请耐心等待！我正在尝试调整查询，但遇到问题。
I have two tables:
我有两个表：
SKU
------
SKUIDX (Unique index)
CLRIDX (Index)
..
..
IMPCOST_CLR
-----------
ICCIDX (Unique index)
CLRIDX (Index)
...
..
When I do a select * from SKU where clridx = 122, I can see that it is using the index in the explain plan (it says TABLE ACCESS.. INDEX, it says the index name under OBJECT_NAME and the options is RANGE SCAN).
当我执行 a 时select * from SKU where clridx = 122，我可以看到它正在使用解释计划中的索引（它说 TABLE ACCESS.. INDEX，它说 OBJECT_NAME 下的索引名称，选项是 RANGE SCAN）。
Now, when I try to join on the same field, it doesn't appear to use the index (it says TABLE ACCESS.. HASH JOIN and under options, it says FULL).
现在，当我尝试加入同一个字段时，它似乎没有使用索引（它说 TABLE ACCESS .. HASH JOIN 和选项下，它说 FULL）。
What should I be looking for to try and see why it isn't using the index? Sorry, I'm not sure what commands to type to show this so please let me know if you need more information.
我应该寻找什么来尝试看看它为什么不使用索引？抱歉，我不确定要键入哪些命令来显示此内容，因此如果您需要更多信息，请告诉我。
Examples:
1st query:
示例：
第一个查询：
SELECT
*
FROM
AP21.SKU
WHERE
CLRIDX = 100
2nd query:
第二个查询：
SELECT
*
FROM
AP21.IMPCOST_CLR
WHERE
CLRIDX = 100
3rd query:
第三个查询：
SELECT
*
FROM
AP21.SKU
INNER JOIN
AP21.IMPCOST_CLR ON
IMPCOST_CLR.CLRIDX = SKU.CLRIDX
回答by APC
Look at this query:
看看这个查询：
SELECT
*
FROM
AP21.SKU
INNER JOIN
AP21.IMPCOST_CLR ON
IMPCOST_CLR.CLRIDX = SKU.CLRIDX
It has no additional predicates. So you are joining all the rows in SKU to all the rows in IMPCOST_CLR. Furthermore you are selecting all the columns from both tables.
它没有额外的谓词。因此，您将 SKU 中的所有行连接到 IMPCOST_CLR 中的所有行。此外，您正在从两个表中选择所有列。
This means Oracle has to read the entirety of both tables. The most efficient way of doing this is to use Full Table Scan, to scoop up all the rows in multi-block reads, and use hashing to match the values of the joining.
这意味着 Oracle 必须读取两个表的全部内容。最有效的方法是使用全表扫描，在多块读取中获取所有行，并使用散列来匹配连接的值。
Basically, it's a set operation, which is what SQL does very well, whereas indexed reads are more RBAR. Now, if you altered the third query to include an additional predicate, such as
基本上，它是一个集合操作，这是 SQL 做得很好的，而索引读取更多的是RBAR。现在，如果您更改第三个查询以包含附加谓词，例如
WHERE SKU.CLRIDX = 100
you would most likely see the access path revert to INDEX RANGE SCAN . Because you're only selecting a comparative handful of rows, so the indexed read is the more efficient path once again.
您很可能会看到访问路径恢复为 INDEX RANGE SCAN 。因为您只选择相对较少的行，所以索引读取再次成为更有效的路径。
"The query im trying to tune is hundreds of much longer, but breaking it down and taking it step by step! "
“我试图调整的查询要长数百倍，但分解并逐步进行！”
This is a good technique but you need to understand how the Oracle optimzer works. There's lots of information in Explain Plan. Find out more.Pay attention to the value in the Rowscolumn for each step. That tells you how many rows the Optimizer expects to get from the operation. You will see very different values for the first two queries compared to the third.
这是一种很好的技术，但您需要了解 Oracle 优化器的工作原理。解释计划中有很多信息。 了解更多。注意Rows每一步的列中的值。这告诉您优化器期望从操作中获得多少行。与第三个查询相比，您会看到前两个查询的值大不相同。
回答by Markus Winand
Now, when I try to join on the same field, it doesn't appear to use the index (it says TABLE ACCESS.. HASH JOIN and under options, it says FULL).
现在，当我尝试加入同一个字段时，它似乎没有使用索引（它说 TABLE ACCESS .. HASH JOIN 和选项下，它说 FULL）。
It's because HASH JOIN doesn't use (need) indexes on the join predicates:
这是因为 HASH JOIN 不在连接谓词上使用（需要）索引：
http://use-the-index-luke.com/sql/join/hash-join-partial-objects
http://use-the-index-luke.com/sql/join/hash-join-partial-objects
