为什么此查询会导致 Oracle 中的 MERGE JOIN CARTESIAN？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3459066/
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
Why does this query result in a MERGE JOIN CARTESIAN in Oracle?
提问by Mark Sherretta
Here is my query:
这是我的查询：
select count(*)
from email_prod_junc j
inner join trckd_prod t5 on j.trckd_prod_sk = t5.trckd_prod_sk
inner join prod_brnd b on t5.prod_brnd_sk = b.prod_brnd_sk
inner join email e on j.email_sk = e.email_sk
inner join dm_geography_sales_pos_uniq u on (u.emp_sk = e.emp_sk and u.prod_brnd_sk = b.prod_brnd_sk)
The explain plan says:
解释计划说：
Cartesian Join between DM_GEOGRAPHY_SALES_POS_UNIQ and EMAIL_PROD_JUNC.
DM_GEOGRAPHY_SALES_POS_UNIQ 和 EMAIL_PROD_JUNC 之间的笛卡尔连接。
I don't understand why because there is a join condition for each table.
我不明白为什么，因为每个表都有一个连接条件。
采纳答案by Mark Sherretta
I solved this by adding the ORDERED hint:
我通过添加 ORDERED 提示解决了这个问题：
select /*+ ordered */
I got the information from here
我从这里得到了信息
If you specify the tables in the order you want them joined and use this hint, Oracle won't spend time trying to figure out the optimal join order, it will just join them as they are ordered in the FROM clause.
如果您按照您希望它们加入的顺序指定表并使用此提示，Oracle 将不会花时间试图找出最佳的连接顺序，它只会按照它们在 FROM 子句中的顺序进行连接。
回答by Allan
Without knowing your indexes and the full plan, it's hard to say why this is happening exactly. My best guess is that EMAIL_PROD_JUNC and DM_GEOGRAPHY_SALES_POS_UNIQ are relatively small and that there's an index on TRCKD_PROD(trckd_prod_sk, prod_brnd_sk). If that's the case, then the optimizer may have decided that the Cartesian on the two smaller tables is less expensive than filtering TRCKD_PROD twice.
在不知道您的索引和完整计划的情况下，很难说为什么会发生这种情况。我最好的猜测是 EMAIL_PROD_JUNC 和 DM_GEOGRAPHY_SALES_POS_UNIQ 相对较小，并且在 TRCKD_PROD(trckd_prod_sk, prod_brnd_sk) 上有一个索引。如果是这种情况，那么优化器可能已经决定两个较小表上的笛卡尔比过滤 TRCKD_PROD 两次要便宜。
回答by RMorrisey
I would speculate that it happens because of the on (x and y) condition of the last inner join. Oracle probably doesn't know how to optimize the multi-statement condition, so it does a full join, then filters the result by the condition after the fact. I'm not really familiar with Oracle's explain plan, so I can't say that with authority
我推测它的发生是因为最后一个内部连接的 on (x 和 y) 条件。Oracle 可能不知道如何优化多语句条件，所以它做了一个全连接，然后在事后按条件过滤结果。我对Oracle的解释计划不是很熟悉，所以我不能这么说
Edit
编辑
If you wanted to test this hypothesis, you could try changing the query to:
如果您想测试这个假设，您可以尝试将查询更改为：
inner join dm_geography_sales_pos_uniq u on u.emp_sk = e.emp_sk
where u.prod_brnd_sk = b.prod_brnd_sk
and see if that eliminates the full join from the plan
看看这是否从计划中消除了完全连接
