我注意到Oracle 11中出现了一个奇怪的FULL OUTER JOIN行为.我正在从HR模式加入表,特别是EMPLOYEES和DEPARTMENTS.
例如,以下查询返回123行：
SELECT * FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id
但是,要理解的是什么 – 当我在select子句中放入一组特定的列时,查询将返回122行(缺少的行是针对没有分配部门的员工 – 另一个是使用左连接返回的行)与内连接相比)：
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(*)有什么区别？
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值.这就是导致它从结果中排除一行的原因.
我现在能想到的唯一非bug解释是你在DISABLE RELY模式下以某种方式创建了约束,以便优化器认为该字段不能包含NULL.在这种情况下,在约束中给出不正确的信息时使用索引是正确的.但是,似乎RELY选项不适用于NOT NULL约束,所以我不知道这可能是什么问题.尽管如此,请仔细查看表中的所有约束.
除此之外,甲骨文网站上有大量关于全外连接错误结果的错误.你可能会遇到其中一个.在其中的一些情况下,解决方法是禁用“本机”完全外部联接,您可以使用此语句为当前会话执行此操作：
alter session set "_optimizer_native_full_outer_join"=off;
