ORA-01719 에러

오라클 23.1 버전부터 IN 조인 조건에 대해 ORA-01719 에러가 발생하지 않습니다.

아래는 V$SYSTEM_FIX_CONTROL 뷰에서 ORA-01719 에러와 관련된 Fix Control을 조회한 결과입니다.

SELECT bugno, value, sql_feature, description, optimizer_feature_enable
  FROM v$system_fix_control
 WHERE bugno IN (6610822, 32436948);

   BUGNO VALUE SQL_FEATURE                    DESCRIPTION                                   OPTIMIZER_FEATURE_ENABLE
-------- ----- ------------------------------ --------------------------------------------- ------------------------
 6610822     1 QKSFM_CBO_6610822              allow (+) in OR clause                        10.2.0.5
32436948     1 QKSFM_TRANSFORMATION_32436948  Allow disjunction in outer join in more cases 23.1.0

2 rows selected.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 (c1, c2, c3) AS SELECT ROWNUM, ROWNUM, ROWNUM FROM XMLTABLE ('1 to 100');
CREATE TABLE t2 (c1, c2, c3) AS SELECT ROWNUM, ROWNUM, ROWNUM FROM XMLTABLE ('1 to 100');

 

아래는 6610822 Fix Control을 테스트한 결과입니다.

-- 2-1
SELECT *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1 AND b.c2(+) IN (1, 2);
...
100 rows selected.

-- 2-2
SELECT /*+ OPT_PARAM('_fix_control' '6610822:0') */
       *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1 AND b.c2(+) IN (1, 2);

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

 

아래는 32436948 Fix Control을 테스트한 결과입니다.

-- 3-1
SELECT *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1 AND b.c2(+) IN (a.c2, a.c3);
...
100 rows selected.

-- 3-2
SELECT /*+ OPT_PARAM('_fix_control' '32436948:0') */
       *
  FROM t1 a, t2 b
 WHERE b.c1(+) = a.c1 AND b.c2(+) IN (a.c2, a.c3);

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

 

ANSI 조인 구문은 IN 조인 조건을 사용해도 에러가 발생하지 않습니다. 아래는 11.2 버전에서 테스트한 결과입니다.

-- 4-1
SELECT *
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c1
   AND b.c2 IN (1, 2);
...
100 rows selected.

-- 4-2
SELECT *
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c1
   AND b.c2 IN (a.c2, a.c3);
...
100 rows selected.