查询是否进行笛卡尔连接，除非
我有一个应该返回 2 行的查询。但是，它返回 48 行。它的行为就像正在连接的其中一张表不存在一样。但是，如果我将该表中的一列添加到 select 子句，而不更改查询的 from 或 where 部分，则它会返回 2 行。
以下是“解释计划”的内容，在选择中没有“m.*”： 
这是在选择中添加 m.* 后的情况： 
任何人都可以解释为什么它应该这样做吗？
更新：我们仅在一个系统上遇到此问题，而在另一个系统上没有遇到此问题。 DBA 验证了存在问题的问题是运行时 optimizer_features_enable 设置为 10.2.0.5，而没有发生问题的情况是运行时 optimizer_features_enable 设置为 10.2.0.4。不幸的是，客户站点运行的是 10.2.0.5。
I've got a query that's supposed to return 2 rows. However, it returns 48 rows. It's acting like one of the tables that's being joined isn't there. But if I add a column from that table to the select clause, with no changes to the from or where parts of the query, it returns 2 rows.
Here's what "Explain plan" says without the "m.*" in the select:
Here it is again after adding m.* in the select:
Can anybody explain why it should behave this way?
Update: We only had this problem on one system and not another. The DBA verified that the one with the problem is running optimizer_features_enable set to 10.2.0.5, and the one where it doesn't happen is running optimizer_features_enable set to 10.2.0.4. Unfortunately the customer site is running 10.2.0.5.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是关于 10gR2 中引入的连接消除：
也许这是一种相关的错误。请查看这篇文章。
It's about a join elimination that was introduced in 10gR2:
Maybe that's kind of related bug or so. Have a look at this article.
看起来像一个错误。有什么限制？
从逻辑上讲，如果 MASTERSOURCE_FUNCTION 中的所有行都具有函数 NON-OSDA，则不会排除任何行（或者如果没有任何行具有该值，则将排除所有行）。
更进一步，如果 MASTERSOURCE 中的每一行在 MASTERSOURCE_FUNCTION 中都有一个或零个非 OSDA 行，那么它应该是排除的候选者。但 MASTERSOURCE ID 和 NAME 之间也需要是一对一的。
我将从 ACCOUNTSOURCE 中提取 48 行的 ROWID，然后跟踪 MASTERSOURCE ID 和 NAME，并查看这些行被重复或未被排除的原因。也就是说，MASTERSOURCE 中是否有 12 个重复名称，通过 NOVALIDATE 约束预计该名称是唯一的。
Looks like a bug. What are the constraints ?
Logically, if all rows in MASTERSOURCE_FUNCTION had the function NON-OSDA then that wouldn't exclude any rows (or if none had that value, then all rows would be excluded).
Going one step further, if every row in MASTERSOURCE had one or zero NON-OSDA rows in MASTERSOURCE_FUNCTION, then it should be a candidate for exclusion. But there would also need to be a one-to-one between the MASTERSOURCE ID and NAME.
I'd pull the ROWIDs from ACCOUNTSOURCE for the 48 rows, then track the MASTERSOURCE ID and NAME and see on what grounds those rows are being duplicated or not excluded. That is, are there 12 duplicate names in MASTERSOURCE where it is expected to be unique through a NOVALIDATE constraint.