오라클 파티션 Exchange (파티션 익스체인지)

파티션테이블(Partition Table)의 한개 파티션과 일반 테이블을 교환하는 것을 Partition Exchange 라고 합니다.

이게 필요한 이유는 파티션의 일부만 떼어내서 작업하고 다시 붙이고 싶은 경우, 로딩한 데이터를 파티션에 끼워넣고 싶은 경우 등입니다.

뭐, 파티션 테이블에 직접 작업을 해도 되겠지만,

작업이 완전한지 검증되기 전까지는 운영중인 파티션테이블을 함부로 건들고 싶지 않기때문에 이런 테크닉을 이용합니다.

 

또, 대량의 Update 나 Delete 작업을 해야하는 경우,

이런 DML 작업은 엄청난 양의 Undo 와 Redo 를 만들게 되는데, 이런 부담을 줄이기 위한 방안으로 CTAS 또는 Insert 로 변환해서 작업을 할 수 있습니다. 원테이블에 Update/Delete 하는 대신 임시테이블로 변경데이터를 Insert 하거나, Delete 되지 않아야 하는 데이터만 Insert 한 후, 이 테이블을 파티션과 Exchange 하는 방식입니다. 이렇게하면 Undo 나 Redo 를 획기적으로 줄이면서 작업을 안정적이고 빠르게 끝낼 수 있습니다.

참조)  >> CTAS로 대량데이터 Update, Delete 작업 빠르게 하는 방법 <<

 

아래와 같이 sales_no 컬럼을 파티션키로 하는 Range 파티션에서...

-- 1) 파티션 테이블 생성
create table SALES (
  sales_no       number,
  sale_year      number,
  sale_month     number,
  sale_day       number,
  customer_name  varchar2(30),
  birth_date     date,
  price          number
)
partition by range (sales_no)
(
  partition SALES_P1 values less than (10),
  partition SALES_P2 values less than (20),
  partition SALES_P3 values less than (maxvalue)
);

-- 2) 파티션테이블에 데이터 입력
insert into SALES values ( 1, 2004, 05, 02, 'Sophia', to_date('19740502','yyyymmdd'), 65000);
insert into SALES values ( 2, 2005, 03, 02, 'Emily',  to_date('19750302','yyyymmdd'), 23000);
insert into SALES values (11, 2006, 08, 02, 'Olivia', to_date('19760802','yyyymmdd'), 34000);
insert into SALES values (12, 2007, 02, 02, 'Amelia', to_date('19770202','yyyymmdd'), 12000);
insert into SALES values (21, 2008, 04, 02, 'Chloe',  to_date('19780402','yyyymmdd'), 55000);

-- 3) 로컬 파티션 인덱스 생성
create index IDX_SALES_01 on SALES (sales_no) LOCAL;

 

파티션에 5건의 데이터를 입력하고, 로컬 파티션 인덱스를 만들었습니다.

아래와 같이 3개의 파티션에 나뉘어져 데이터가 들어갔습니다.

 

 

다음은 Exchange(교환)에 사용할 임시 테이블을 만들겠습니다.

인덱스도 파티션테이블과 똑같이 만들어줍니다.

 

-- 1) 임시 테이블 생성
create table SALES_TEMP (
  sales_no       number,
  sale_year      number,
  sale_month     number,
  sale_day       number,
  customer_name  varchar2(30),
  birth_date     date,
  price          number
);

-- 2) 임시테이블에 데이터 입력
insert into SALES_TEMP values (13, 2019, 04, 24, 'Avengers1', to_date('20130526','yyyymmdd'), 10000);
insert into SALES_TEMP values (14, 2019, 04, 24, 'Avengers2', to_date('20130526','yyyymmdd'), 10000);
insert into SALES_TEMP values (15, 2019, 04, 24, 'Avengers3', to_date('20130526','yyyymmdd'), 10000);

-- 3) 일반 인덱스 생성
create index IDX_SALES_TEMP_01 on SALES_TEMP (sales_no);

 

SALES_TEMP 테이블은 다음과 같습니다.

 

 

이제, 임시로 만든 SALES_TEMP 테이블과 SALES 테이블의 SALES_P2 파티션을 교환(Exchange) 해 보겠습니다.

 

alter table SALES
  exchange partition SALES_P2 with table SALES_TEMP
  including indexes;
     --> Index도 함께 Exchange 됨

 

 

Exchange 명령 수행후, SALES 테이블을 조회해보면 중간에 Avengers 1,2,3 데이터가 들어가 있는 것을 확인할 수 있습니다.

기존에 그 위치에 있었던 Olivia, Amelia 데이터는 SALES_TEMP 테이블에 존재하게 됩니다.

 

 

또한, 아래와 같이 조회해 보면 인덱스도 모두 정상인 것을 확인할 수 있습니다.

 

 

이 처럼, 로컬 파티션 인덱스의 경우, Partition Exchange 할 때, 임시 테이블에 미리 인덱스를 만들어뒀다면,

그 임시테이블의 인덱스가 파티션 인덱스로 들어오기 때문에, 추가적으로 인덱스 리빌드 작업을 해줄 필요가 없습니다.

매우 간편하고, 빠르죠.

 

 

 

하지만, SALES 테이블에 글로벌 파티션 인덱스가 있었다면 ???

 

-- 파티션테이블 글로벌 파티션 인덱스 생성
create index IDX_SALES_02 on SALES (customer_name) GLOBAL
partition by hash (customer_name)
partitions 4;

-- 임시테이블에 인덱스 생성
create index IDX_SALES_TEMP_02 on SALES_TEMP (customer_name);

 

위와 같이 미리 글로벌 파티션 인덱스가 만들어져 있고, 임시테이블에도 같은 인덱스를 만들어주게 되면,

Exchange 할때 다음과 같이 ORA-14098 에러가 발생합니다.

 

 

임시테이블(SALES_TEMP) 에 있는 인덱스는 모두 Exchange 대상인 파티션의 로컬 인덱스와 교환대상으로 간주를 합니다. 따라서 Exchange 실행시 customer_name 으로 되어 있는 로컬 파티션 인덱스가 있는지를 찾고 없으니까 mismatch 에러가 발생하는 것입니다.

 

SALES_TEMP 에서 IDX_SAELS_TEMP_02 인덱스를 제거한 후, 실행하면 exchange 가 성공합니다.

 

 

하지만, 글로벌 파티션 인덱스인 IDX_SALES_02 인덱스는 Unusable 상태가 되는 것을 확인할 수 있습니다.

 

이 경우, 이 글로벌 인덱스는 다시 리빌드(rebuild) 해줘야 합니다.

 

 

파티션 인덱스 리빌드 할때 일반 인덱스처럼 그냥 하면 ORA-14086 에러를 만나게 됩니다.

통째로 리빌드 하지말라는 얘기입니다. 통째로 할거면 인덱스를 drop 하고 다시 create 하라고 가이드합니다.

 

아래와 같이 파티션을 일일이 지정해서 리빌드 해야 합니다.

뭐지 이건? 이게 통째로 리빌드하는 것과 뭔차이인지?? 알수는 없지만 이렇코롬 되어있습니다...

 

 

이제 모든 인덱스가 Usable 상태로 되어 있는것을 확인할 수 있습니다.

 

 

위 예제에서 인덱스를 파티션마다 rebuild 작업을 해주는게 매우 귀찮은 일입니다.

파티션 갯수가 많고 Global Index 도 많은 경우 특히 귀찮죠.

 

다음과 같은 옵션을 주어 Exchange 할때 자동으로 Global Index 에 대해 리빌드하도록 지정할 수 있습니다.

물론 이렇게 하면 편하긴 한데, Exchange 하는 시간이 많이 오래 걸리겠죠??

 

alter table SALES
  exchange partition SALES_P2 with table SALES_TEMP
  without validation
  update global indexes;
     --> Global Index 리빌드 포함.

 

update global indexes 구문이 글로벌 인덱스를 리빌드하는 것이고,

without validation 구문은 Exchange 하는 데이터가 파티션 조건에 위배되지 않는지를 체크할지 말지 지정하는 옵션입니다.

 

 

▶▶▶ 오라클 파티션 종합 페이지 바로가기