Oracle/Tuning2019. 11. 29. 14:39

SELECT 문과 DML 문의 쿼리 변환 차이

SELECT 문과 DML 문은 쿼리 변환에 차이가 있고, 이로 인해 의도치 않은 에러가 발생할 수 있다.


테스트를 위해 아래와 같이 테이블을 생성하자.

1
2
3
4
5
6
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
 
CREATE TABLE t1 (c1 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER);

아래 쿼리는 아우터 조인되는 테이블에 서브 쿼리를 사용했다. 11.2 버전은 에러가 발생하지만, 12.2 버전은 VW_DCL 인터널 뷰 쿼리 변환로 인해 에러가 발생하지 않는다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 2-1 : 11.2
SELECT *
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c1
   AND b.c2 = (SELECT MAX (c2) FROM t2);
 
ORA-01799: 열은 하위 질의에 포괄 조인될 수 없습니다.
 
-- 2-2 : 12.2
SELECT *
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c1
   AND b.c2 = (SELECT MAX (c2) FROM t2);
 
-------------------------------------------------
| Id  | Operation             | Name            |
-------------------------------------------------
|   0 | SELECT STATEMENT      |                 |
|*  1 |  HASH JOIN OUTER      |                 |
|   2 |   TABLE ACCESS FULL   | T1              |
|   3 |   VIEW                | VW_DCL_D4BF72F2 |
|*  4 |    TABLE ACCESS FULL  | T2              |
|   5 |     SORT AGGREGATE    |                 |
|   6 |      TABLE ACCESS FULL| T2              |
-------------------------------------------------

SELECT 문을 INSERT 문의 서브 쿼리로 사용하면 12.2 버전도 에러가 발생한다. 쿼리 변환에 제약이 있는 것으로 보인다.

1
2
3
4
5
6
7
8
9
10
11
-- 3 : 12.2
INSERT
  INTO t1
SELECT a.*
  FROM t1 a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c1
   AND b.c2 = (SELECT MAX (c2) FROM t2);
 
ORA-01799: 열은 하위 질의에 포괄 조인될 수 없습니다.

아래 쿼리처럼 인라인 뷰를 사용하는 편이 바람직하다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 4
INSERT
  INTO t1
SELECT a.*
  FROM t1 a
  LEFT OUTER
  JOIN (SELECT *
          FROM t2
         WHERE c2 = (SELECT MAX (c2) FROM t2))b
    ON b.c1 = a.c1;
 
-----------------------------------------
| Id  | Operation                | Name |
-----------------------------------------
|   0 | INSERT STATEMENT         |      |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |
|*  2 |   HASH JOIN OUTER        |      |
|   3 |    TABLE ACCESS FULL     | T1   |
|   4 |    VIEW                  |      |
|*  5 |     TABLE ACCESS FULL    | T2   |
|   6 |      SORT AGGREGATE      |      |
|   7 |       TABLE ACCESS FULL  | T2   |
-----------------------------------------


저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

계층 코드와 예상 카디널리티  (0) 2019.12.27
스칼라 서브 쿼리의 버퍼 Pinning 효과  (0) 2019.12.24
POWER 함수의 CPU 연산 #2  (0) 2019.11.23
WINDOW SORT로 인한 성능 저하  (0) 2019.11.22
INDEX MIN/MAX 오퍼레이션이 동작하지 않는 사례  (0) 2019.10.29
Posted by 정희락_

티스토리툴바