Partition Exchange의 함정

Partition Exchange(이하 PE)는 특정 파티션과 다른 테이블의 데이터를 바꿔 치기 하는 기능이다. 많은 사람들이 PE를 실행 할 때 성능향상을 위하여 Without Validation 옵션을 자주 사용한다. 이 옵션은 Constraint의 Validation Check를 하지 않기 때문에 성능이 향상된다. 그렇지만 조건에 맞지 않는 값이 파티션에 들어갈 수 있다는 단점도 있다. 그래서 많은 사람들이 PE를 실행 하기 전에 NOT NULL 이나 FK등의 제약조건을 검사한 후에 PE를 실행한다.

 

하지만.......

그러한 검사를 하여도 여전히 오류는 존재할 수 있다는 걸 아는 사람은 많지 않다. 이 내용을 모른다면 데이터를 찾을 수 없는 심각한 상황을 맞게 된다. 2009년 4월에 발생한 장애 상황을 보자.

 

박대리 : 현재 파티션이 2009년 3월 까지 만들어져 있고 이후로 파티션을 Split 하지 않아서 2009년 4월 데이터가 MAX 파티션에 들어가 있습니다. 파티션을 Split 하여 4월 파티션을 만들고 거기에 데이터를 옮기고 싶습니다. 어떻게 하면 되나요?

 

김과장 : 빈 테이블을 하나 만들고 MAX 파티션과 PE를 실행하면 됩니다. 그 후에 파티션을 Split 하여 4월 파티션을 만든 후에 다시 PE를 실행한다면 해결됩니다. 단 데이터의 건수가 많으므로 Without Validation 옵션을 주면 성능이 향상됩니다.

 

박대리 : 알겠습니다.

 

대화를 끝낸 A씨는 김과장이 알려준 대로 하였다. 작업은 밤 늦게 시작하여 정상적으로 끝났지만 문제는 다음날 아침에 발생하였다.

 

사용자 : 데이터 중에 한 건이 조회되지 않습니다. 어제는 조회가 되었는데 갑자기 오늘부터 안되네요.

 

박대리: 그럴리가요?

 

이상하지 않은가?
데이터를 두 번 바꿔 치기 했을 뿐 데이터를 조작(Update)하거나 삭제한적이 없다. 그런데 조회가 되질 않는다니.....

 

이제 위의 장애 상황을 직접 만들어보자.

 

--테스트 환경 Oracle 11.1.0.6

--파티션 테이블과 인덱스 그리고 Constraint 생성

CREATE TABLE TB_EX_TEST

( VAL1        NUMBER,

 TR_DT       CHAR(8)     NOT NULL )

PARTITION BY RANGE (TR_DT)

(

    PARTITION PT_R200902 VALUES LESS THAN ('20090301') , --2월 파티션

    PARTITION PT_R200903 VALUES LESS THAN ('20090401') , --3월 파티션

    PARTITION PT_RMAX VALUES LESS THAN (MAXVALUE)    --max 파티션

);

 

CREATE UNIQUE INDEX PK_EX_TEST ON TB_EX_TEST (VAL1, TR_DT ) LOCAL;

 

ALTER TABLE TB_EX_TEST

ADD CONSTRAINT PK_EX_TEST PRIMARY KEY (VAL1, TR_DT);

 

-- 데이터 입력

 

INSERT INTO TB_EX_TEST VALUES(1 ,'20090201'); --2월 데이터

INSERT INTO TB_EX_TEST VALUES(2 ,'20090202'); --2월 데이터

INSERT INTO TB_EX_TEST VALUES(3 ,'20090203'); --2월 데이터

...중간생략

INSERT INTO TB_EX_TEST VALUES(11,'20090301'); --3월 데이터

INSERT INTO TB_EX_TEST VALUES(12,'20090302'); --3월 데이터

INSERT INTO TB_EX_TEST VALUES(13,'20090303'); --3월 데이터

...중간생략           

INSERT INTO TB_EX_TEST VALUES(21,'20090401'); --4월 데이터

INSERT INTO TB_EX_TEST VALUES(22,'20090402'); --4월 데이터

INSERT INTO TB_EX_TEST VALUES(23,'20090403'); --4월 데이터

INSERT INTO TB_EX_TEST VALUES(24,'30090404');  --잘못된 데이터 입력.

 

COMMIT;

 

--PE를 실행하기 위한 Temp 테이블 생성

CREATE TABLE TMP_TB_EX_TEST

( VAL1        NUMBER,

 TR_DT       CHAR(8)     NOT NULL ) ;

 

CREATE UNIQUE INDEX PK_TMP_TB_EX_TEST ON TMP_TB_EX_TEST (VAL1, TR_DT);

 

ALTER TABLE TMP_TB_EX_TEST

ADD CONSTRAINT PK_TMP_TB_EX_TEST PRIMARY KEY (VAL1, TR_DT);

 

이제 모든 준비가 끝났으므로 PE를 실행해보자.

 

ALTER TABLE TB_EX_TEST EXCHANGE PARTITION

PT_RMAX  WITH TABLE TMP_TB_EX_TEST WITHOUT VALIDATION;

 

SELECT * FROM TMP_TB_EX_TEST;

 

VAL1       TR_DT  
---------- --------
        21 20090401
        22 20090402
        23 20090403
        24 30090404  -->원래는 '20090404' 이나 업무팀에서 잘못된 데이터를 넣은 건임. 
 

4 rows selected

 

 

PE를 성공적으로 수행하여 잘못된 데이터를 포함한 모든 데이터가 TEMP 테이블로 들어갔다 . 이제 MAX 파티션을 Split 하여 4월 파티션을 만들고 그 파티션과 TEMP 테이블을 이용하여 PE를 실행하면 모든 작업이 완료된다.

 

--MAX 파티션을 Split 하여 4월달 파티션 생성

ALTER TABLE TB_EX_TEST SPLIT PARTITION 

PT_RMAX AT ('20090501') INTO (PARTITION PT_R200904, PARTITION PT_RMAX);

 

Table altered.

 

--TEMP 테이블의 데이터를 이용하여 4월 파티션에 PE 수행

ALTER TABLE TB_EX_TEST EXCHANGE PARTITION 

PT_R200904  WITH TABLE TMP_TB_EX_TEST WITHOUT VALIDATION;

 

Table altered.

 

-- 2009년 파티션의 인덱스를 REBUILD 해준다.

ALTER INDEX PK_EX_TEST REBUILD PARTITION PT_R200904;

 

Index altered.

 

이제 모든 작업이 끝났으므로 2009년 4월 데이터를 조회해보자.

 

SELECT *

  FROM TB_EX_TEST

 WHERE TR_DT LIKE ‘200904%’;

 

VAL1       TR_DT  

---------- --------

        21 20090401

        22 20090402

        23 20090403

 

3 rows selected.

 

3건이 조회되었고 여기까지는 정상적이다. 2009년 4월을 지정하였으므로 잘못된 데이터가 조회되지 않음은 당연한 것이다. 하지만 충격적이게도 잘못된 데이터를 where 조건에 직접 입력해도 전혀 조회되지 않는다. 아래 SQL의 결과를 보라.

 

SELECT *

  FROM TB_EX_TEST

 WHERE TR_DT='30090404';

 

no rows selected.

 

단 한 건도 나오지 않는다

이상하지 않은가? 데이터는 존재하는데 조회되지는 않는다니.... 버그인가?

전혀 아니다. Partition Pruning이라는 기능 때문이다. 2009년 4월 보다 큰 값을 where 조건에 주게 되면 MAX 파티션을 가리키게 되므로 조회가 되지 않는 것이다. 다시 말하면 잘못된 데이터는 2009년 4월 파티션에 존재하지만 WHERE 조건은 MAX 파티션을 지정하였으므로 데이터가 조회되지 않는 것이다.  

 

그럼 어떻게 조회해야 하나?

조회가 되어야 잘못된 값을 수정할 것이 아닌가? 이때는 아래처럼 FROM 절에서 특정 파티션을 지정하면 된다.

SELECT ROWID, VAL1, TR_DT
  FROM TB_EX_TEST PARTITION (PT_R200904);

 

ROWID                    VAL1 TR_DT  

------------------ ---------- --------

AAASWxAAEAAAYf+AAA         21 20090401

AAASWxAAEAAAYf+AAB         22 20090402

AAASWxAAEAAAYf+AAC         23 20090403

AAASWxAAEAAAYf+AAD         24 20090404

 

4 rows selected.

 

박대리는 MAX 파티션에 4월 데이터만 있을 것으로 예상하였고 3009년 4월 데이터가 있을 줄은 꿈에도 생각하지 못한 것이다. 하지만 현실의 데이터는 언제나 지저분하게 마련이다.

 

그렇다면 잘못된 데이터를 어떻게 수정해야 하나?

UPDATE 시에 3009년 4월 4일을 WHERE절에 지정하면 데이터가 없으므로 수정에 실패한다. 따라서 ROWID를 찾아서 UPDATE 해야 한다.

UPDATE TB_EX_TEST
   SET TR_DT = '20090404'
 WHERE ROWID =  CHARTOROWID('AAASWxAAEAAAYf+AAD');

1 row updated.

COMMIT;

Commit complete.

  

 

결론

Partition Exchange 시에 Without Validation을 지정하게 되면 Constraint 뿐만 아니라 파티션의 경계도 무시된다. 따라서 Exchange 할 소스테이블의 파티션 key 값에 대한 검증을 하여 잘못된 값을 바로잡아야 한다. 그렇지 않으면 데이터가 사라진 것처럼 보일 것이다.

지금 주위에 있는 사람에게 질문해 보라. WHERE 절에 특정월을 지정하는 것과 FROM 절에 특정월의 파티션명을 지정하는 것의 결과가 다를 수 있는지 없는지를. 아마 많은 사람들이 결과는 다를 수 없다라고 이야기 할 것인데 정답은 다를 수 있다는 것이다. 여러분이 위에서 본 것처럼....