首页 文章

在Oracle 12c中，具有外连接的查询行为有所不同

提问于
浏览 次
14

有关Oracle 12c上缺少数据的问题 .

我看了一下代码，发现了一个适用于mysql，mssql，oracle 11g的查询，但在oracle 12c中有不同的行为 .

我已经概括了表结构和查询，并重现了这个问题 .

create table thing (thing_id number, display_name varchar2(500));
create table thing_related (related_id number, thing_id number, thing_type varchar2(500));
create table type_a_status (related_id number, status varchar2(500));
create table type_b_status (related_id number, status varchar2(500));

insert into thing values (1, 'first');
insert into thing values (2, 'second');
insert into thing values (3, 'third');
insert into thing values (4, 'fourth');
insert into thing values (5, 'fifth');
insert into thing_related values (101, 1, 'TypeA');
insert into thing_related values (102, 2, 'TypeB');
insert into thing_related values (103, 3, 'TypeB');
insert into thing_related (related_id, thing_id) values (104, 4);

insert into type_a_status values (101, 'OK');
insert into type_b_status values (102, 'OK');
insert into type_b_status values (103, 'NOT OK');

运行查询：

SELECT t.thing_id AS id, t.display_name as name,
       tas.status as type_a_status,
       tbs.status as type_b_status
FROM thing t LEFT JOIN thing_related tr 
  ON t.thing_id = tr.thing_id
LEFT JOIN type_a_status tas 
  ON (tr.related_id IS NOT NULL 
      AND tr.thing_type = 'TypeA' 
      AND tr.related_id = tas.related_id)
LEFT JOIN type_b_status tbs 
  ON (tr.related_id IS NOT NULL 
      AND tr.thing_type = 'TypeB' 
      AND tr.related_id = tbs.related_id)

在Oracle 11g上给出（这里是SQL Fiddle）：

ID | NAME   | TYPE_A_STATUS | TYPE_B_STATUS
 1 | first  |            OK | (null)
 2 | second |        (null) | OK
 3 | third  |        (null) | NOT OK
 4 | fourth |        (null) | (null)
 5 | fifth  |        (null) | (null)

Oracle 12c上的相同架构，数据和查询：

ID | NAME   | TYPE_A_STATUS | TYPE_B_STATUS
 1 | first  |            OK | (null)
 2 | second |        (null) | OK
 3 | third  |        (null) | NOT OK
 4 | fourth |        (null) | (null)

似乎第二个两个外部联接未能带回任何东西，因为“thing_related”中没有要加入的行 . 但是我不明白为什么外连接在这种情况下不会像在Oracle 11g，Mysql等中那样返回空值 .

我一直在研究并发现Oracle 12c对外连接有一些增强的文档，但没有任何突出显示会影响这种情况的更改 .

有谁知道为什么这只发生在Oracle 12c上，我最好如何重写它在12c中工作并保持与11g，mysql等的兼容性？

编辑：附加计划 .

Oracle 11g：

enter image description here

Oracle 12c：

enter image description here

sql oracle oracle12c

2 回答

  • 12

    UPDATE: 这已在12.1.0.2中修复 .

    这绝对看起来像12.1.0.1中的错误 . 我建议您通过Oracle支持创建服务请求 . 他们或许可以找到解决方案或更好的解决方法 . 希望Oracle能够在未来版本中为每个人修复它 . 通常，使用支持的最糟糕的部分是重现问题 . 但是，由于您已经有一个非常好的测试用例，这个问题可能很容易解决 .

    有很多方法可以解决这个bug . 但是很难说哪种方法总能奏效 . 您的查询重写现在可以正常工作，但如果优化程序统计信息发生更改，则计划将来可能会更改 .

    在12.1.0.1.0上适用于我的另一个选项是：

    ALTER SESSION SET optimizer_features_enable='11.2.0.3';

    但是你之后的12.1.0.1' . 有一些方法可以在查询提示中嵌入它，例如 /*+ OPT_PARAM('optimizer_features_enable' '11.2.0.3') */ . 但由于某种原因，这里不起作用 . 或者也许您可以暂时为整个系统设置它，并在修复或更好的解决方法可用后将其更改回来 .

    无论您使用哪种解决方案，请记住记录它 . 如果查询看起来很奇怪，那么下一个开发人员可能会尝试“修复”它并遇到同样的问题 .

    回复于
  • 4

    参考：

    ANSI外部联接查询在升级到12.1.0.1后返回错误的结果（文档ID 1957943.1）

    未公开的错误16726638

    在12.1.0.2中修复（我测试了这个）

    解决方法（我在12.1.0.1中测试过）：

    alter session set "_optimizer_ansi_rearchitecture"=false;

    注1957943.1建议作为替代方案：

    optimizer_features_enable = '11.2.0.4';

    但这不起作用 .

    回复于

相关问题