为什么此查询会导致Oracle中的MERGE JOIN CARTESIAN?

问题描述

这是我的查询:

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)

解释计划说:

DM_GEOGRAPHY_SALES_POS_UNIQ和EMAIL_PROD_JUNC之间的笛卡尔联接.

我不明白为什么，因为每个表都有一个连接条件.

解决方案

我通过添加ORDERED提示解决了这个问题:

select /*+ ordered */

我从此处

如果按照希望它们连接的顺序指定表并使用此提示，Oracle不会花时间尝试找出最佳连接顺序，它将按照FROM子句中的顺序将它们连接起来. /p>

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.

I don't understand why because there is a join condition for each table.

解决方案

I solved this by adding the ORDERED hint:

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中的MERGE JOIN CARTESIAN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！