태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Query Transformation의 재미있는 사례 - Oracle의 버그일까, 아니면...

오라클 2008.02.14 14:33
Oracle의 Query 실행 엔진은 크게 두 단계의 작업을 거쳐서 실행 계획을 생성한다.

Transformation: Query를 Rewrite하는 단계이다. View Merge, Subquery Unnest, Join Predicate Pushing, Constraint Check 등을 통해 Oracle은 원래 쿼리를 Rewrite한다.
Optimization: Transform된 Query에 대해 통계 정보 등을 이용해서 최적화된 실행 계획을 생성한다.

Query Transformation은 매우 다양한 방식으로 동작한다. 그 중에서 잘 알려지지 않은 것이 Constraint에 의한 Transformation이다. Constraint에 의한 Query Transformation에 대해서는 앞서 Predicate와 관련된 글에서 잠깐 다룬 적이 있다.

Foreign Key Constraint에 의한 Query Transformation 때문에 잘못된 결과가 나오는 이상한 현상을 통해 이 문제를 한번 다루어 보자.

-- PK/FK 관계를 지닌 테이블을 생성한다.
OWI@joss> CREATE TABLE parent_tab (parent_id NUMBER NOT NULL); 
OWI@joss> ALTER TABLE parent_tab ADD CONSTRAINT parent_pk PRIMARY KEY (parent_id);
OWI@joss> CREATE TABLE child_tab
(child_id NUMBER NOT NULL,
parent_id NUMBER NOT NULL);

OWI@joss> ALTER TABLE child_tab
ADD CONSTRAINT child_pk PRIMARY KEY (child_id);

OWI@joss> ALTER TABLE CHILD_TAB 
ADD CONSTRAINT child_fk FOREIGN KEY (parent_id)
REFERENCES parent_tab (parent_id);
-- 값을 삽입한다. (parent_id = 1, 2)
OWI@joss> INSERT INTO parent_tab VALUES (1);
OWI@joss> INSERT INTO parent_tab VALUES (2);
OWI@joss> INSERT INTO child_tab VALUES (1,1);
OWI@joss> INSERT INTO child_tab VALUES (2,1);
OWI@joss> INSERT INTO child_tab VALUES (3,2);
OWI@joss> INSERT INTO child_tab VALUES (4,2);

-- Constraint을 잠시 Disable한다.
OWI@joss> ALTER TABLE child_tab MODIFY CONSTRAINT child_fk DISABLE VALIDATE;

-- parent_tab에서 parent_id = 1인 데이터를 삭제한다. child_tab에서 parent_id = 1 인 데이터는 고아(Orphant)가 된다.<-- 여기에 Trick이 있다.
OWI@joss> DELETE FROM parent_tab  WHERE parent_id = 1;
OWI@joss> COMMIT;

-- Constraint를 다시 Enable 한다.
OWI@joss>  ALTER TABLE child_tab MODIFY CONSTRAINT child_fk ENABLE VALIDATE;


-- 이제 다음과 같은 쿼리를 수행한다.
-- parent_id = 1인 데이터가 parent_tab에서 삭제되었으므로 결과는 (3,2),(4,2) 두 건만이 나와야 한다.
OWI@joss> select child_tab.*
       from child_tab, parent_tab
      where child_tab.parent_id = parent_tab.parent_id;

한지만 실제 수행 결과는 다음과 같다. 2건이 아닌 네건이 나온다. 전혀 잘못된 결과가 나오는 것이다.
  CHILD_ID  PARENT_ID
---------- ----------
1 1
2 1
3 2
4 2

왜 이런 현상이 발생할까? 실행 계획을 보면 그 이유를 알 수 있다.

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CHILD_TAB | 4 | 104 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------
위의 실행 계획을 보면 parent_tab으로는 아예 가지도 않는 것을 확인할 수 있다. Oracle은 FK Constraint 조건을 이용해 Query를 다음과 같이 Transformation해 버린 것이다.
OWI@joss> select * from child_tab;
Oracle은 FK Constraint 조건이 있으므로 굳이 parent_tab까지 가지 않아도 원하는 결과를 얻을 수 있는 것으로 간주한 것이다. 하지만 우리는 간단한 조작을 통해 child_tab 테이블에 고아(Orphant) 데이터를 만들었다. 이런 이유로 전혀 잘못된 쿼리 결과가 나오게 된다.

아래와 같이 Transformation이 발생하지 않게끔 힌트를 주면 Nested Loop Join이 발생하고 정상적인 결과를 얻을 수 있다.
OWI@joss> select /*+ NO_QUERY_TRANSFORMATION */ child_tab.*
2 from child_tab, parent_tab
3 where child_tab.parent_id = parent_tab.parent_id
4 ;

CHILD_ID PARENT_ID
---------- ----------
3 2
4 2

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 13 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 13 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CHILD_TAB | 4 | 104 | 13 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PARENT_PK | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
위의 현상을 버그라고 봐야 할까? 아마 사람들마다 다른 결론을 낼 것이다.
개인적인 생각으로는 버그라기 보다는 Query Transformation에 의한 원하는 않은 부작용으로 보는 것이 맞을 거 같다. 물론 넓은 의미에서는 버그이겠지만...

Oracle은 매우 적극적으로 Query Transformation을 수행한다는 사실을 기억할 필요가 있겠다. 더구나 10g부터는 Cost Based Transformation(CBO가 아닌 CBT) 기능이 추가되어 이전 버전과 다른 방식으로 Transformation이 발생하게 된다. 항상 실행 계획을 확인해서 Transformation이 발생하는지, 발생한다면 어떤 유형의 Transformation이 발생했는지 확인하는 습관이 필요할 것이다.




신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바