Oracle中完全外部联接的奇怪行为-如何解释? [英] Strange behaviour of full outer join in Oracle - how it could be explained?

问题描述

我注意到Oracle 11中FULL OUTER JOIN的奇怪行为.我正在从HR模式(尤其是EMPLOYEES和DEPARTMENTS)中联接表.

I have noticed a strange behaviour of FULL OUTER JOIN in Oracle 11. I was joining tables from HR schema, particularily EMPLOYEES and DEPARTMENTS.

例如，以下查询返回123行:

For example, the following query returns 123 rows:

    SELECT * FROM employees e
    FULL JOIN departments d ON e.department_id = d.department_id

但是，要理解的是棘手的问题-当我在select子句中放置一组特定的列时，查询将返回122行(缺少行的员工未分配部门，该行会丢失-该行会另外返回与内部联接相比，左联接):

However, what is tricky to understand - when I put a set of particular columns in the select clause, the query will return 122 rows (a missing row is for an employee which has no department assigned - the one which is additionally returned with left join in comparison to inner join):

    SELECT first_name, last_name, department_name FROM employees e
    FULL JOIN departments d on e.department_id = d.department_id

即使我计算行数也返回122(COUNT(*))！到底是怎么回事? SELECT *和SELECT COUNT(*)有什么区别?

Even when I count the rows it returns 122 (COUNT(*))!!! WHAT IS GOING ON? What is the difference between SELECT * and SELECT COUNT(*)?

SELECT * ...的说明计划:

SELECT STATEMENT                                      122
  VIEW                 VW_FOJ_0                       122
    HASH JOIN                          FULL OUTER     122
      Access Predicates
        E.DEPARTMENT_ID = D.DEPARTMENT_ID
      TABLE ACCESS     DEPARTMENTS     FULL            27
      TABLE ACCESS     EMPLOYEES       FULL           107

，对于SELECT COUNT(*) ...:

SELECT STATEMENT                                             1
  SORT                                     AGGREGATE         1
    VIEW               VW_FOJ_0                            122
      HASH JOIN                            FULL OUTER      122
        Access Predicates
          E.DEPARTMENT_ID = D.DEPARTMENT_ID
        INDEX          DEPT_ID_PK          FAST FULL SCAN   27
        INDEX          EMP_DEPARTMENT_IX   FAST FULL SCAN  107

推荐答案

优化器不应选择在第二个查询中使用EMP.DEPT_ID上的索引，因为它可以具有NULL值.这就是导致它从结果中排除一行的原因.

The optimizer should not be choosing to use the index on EMP.DEPT_ID in the second query, since it can have NULL values. This is what's causing it to exclude one row from the results.

我目前唯一能想到的非错​​误的解释是，您已经以某种方式在DISABLE RELY模式下创建了约束，以便优化程序认为该字段不能包含NULL.在这种情况下，给定约束中不正确的信息，使用索引将是正确的.但是，似乎RELY选项不适用于NOT NULL约束，因此我不认为这可能是问题所在.尽管如此，请仔细查看表上的所有约束.

The only non-bug explanation I can think of at the moment is that you have somehow created constraints in DISABLE RELY mode so that the optimizer thinks that the field cannot contain NULLs. In this case it would be correct to use the index given the incorrect information in the constraints. However, it seems the RELY option is not available for NOT NULL constraints, so I don't see how this could be the problem. Nonetheless, take a careful look at all constraints on the tables.

此外，Oracle网站上存在大量关于完全外部联接的错误结果的错误.您可能会打中其中之一.在许多情况下，解决方法是禁用本地"完全外部联接，您可以使用以下语句在当前会话中执行此操作:

That aside, there are a surprising number of bugs on Oracle's site regarding wrong results from full outer joins. You might be hitting one of them. In quite a few of these cases, the workaround is to disable "native" full outer joins, which you can do for your current session with this statement:

alter session set "_optimizer_native_full_outer_join"=off; 

这篇关于Oracle中完全外部联接的奇怪行为-如何解释?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！