외부 조인을 특징으로 하는 쿼리가 Oracle 12c에서 다르게 작동함

외부 조인을 특징으로 하는 쿼리가 Oracle 12c에서 다르게 작동함

오라클 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'에 조인할 행이 없기 때문에 아무것도 가져오지 못하고 있는 것 같습니다.그런데 왜 외부 조인이 오라클 11g, Mysql 등에서와 같이 이 경우에 null을 반환하지 않는지 이해할 수 없습니다.

조사해 본 결과 Oracle 12c에는 외부 조인을 위한 여러 가지 향상된 기능이 포함되어 있었지만, 이에 영향을 미칠 만한 변경 사항은 없었습니다.

Oracle 12c에서만 이러한 현상이 발생하는 이유와 12c에서 작동하고 11g, mysql 등과의 호환성을 유지하기 위해 이를 어떻게 다시 작성해야 하는지 아는 사람이 있습니까?

편집: 첨부된 계획입니다.

Oracle 11g:

Oracle 12c:

업데이트: 이 문제는 12.1.0.2에서 해결되었습니다.

---

이것은 확실히 12.1.0.1의 버그처럼 보입니다.오라클 지원을 통해 서비스 요청을 생성하는 것이 좋습니다.그들은 해결책이나 더 나은 해결책을 찾을 수 있을 것입니다.그리고 바라건대 오라클이 모두를 위한 미래 버전에서 이 문제를 해결할 수 있기를 바랍니다.일반적으로 지원을 받을 때 가장 안 좋은 부분은 문제를 재현하는 것입니다.그러나 이미 매우 우수한 테스트 사례가 있으므로 이 문제는 쉽게 해결할 수 있습니다.

이 버그를 해결하는 데는 여러 가지 방법이 있을 것입니다.하지만 어떤 방법이 항상 효과가 있을지는 알 수 없습니다.지금 쿼리 다시 쓰기가 작동할 수도 있지만 최적화 도구 통계가 변경되면 나중에 계획이 다시 변경될 수도 있습니다.

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') */하지만 어떤 이유에서인지 그것은 여기서 작동하지 않습니다.또는 전체 시스템에 대해 일시적으로 설정한 후 수정 또는 개선된 해결 방법을 사용할 수 있는 경우 다시 변경할 수 있습니다.

어떤 솔루션을 사용하든 반드시 문서화해야 합니다.쿼리가 이상하게 보이면 다음 개발자가 "수정"을 시도하여 동일한 문제를 해결할 수 있습니다.

참조:

12.1.0.1로 업그레이드한 후 잘못된 결과를 반환하는 ANSI 외부 조인 쿼리(Doc 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';

하지만 그것은 효과가 없습니다.

저는 11gR2에서 12c로의 마이그레이션을 계획하고 있으며, 많은 구문이 ANSI에 있습니다.모든 쿼리를 테스트하고 11g 데이터와 비교하는 것은 정말 악몽입니다.대체 세션 집합 "_optimizer_ansi_rearchitecture" 설정 = false이며, 유일한 솔루션이거나 버그가 수정되었습니다.

언급URL : https://stackoverflow.com/questions/19686262/query-featuring-outer-joins-behaves-differently-in-oracle-12c