오라클 파티션(Partition) Exchange option - 오라클 파티션 exchange

Last Updated on 7월 31, 2021 by Jade(정현호)

1. Test 환경

2. 테스트 테이블 생성

■ 파티션 테이블 생성

CREATE TABLE SCOTT.PE_TEST (SEQ NUMBER) PARTITION BY RANGE(SEQ)
( PARTITION TEST_S0001 VALUES LESS THAN (1000),
  PARTITION TEST_S0002 VALUES LESS THAN (2000),
  PARTITION TEST_S0003 VALUES LESS THAN (3000),
  PARTITION TEST_S0004 VALUES LESS THAN (4000));

 ALTER TABLE SCOTT.PE_TEST ENABLE ROW MOVEMENT;

■ 동일 구조의 일반테이블 생성 및 데이터 입력, 데이터는 3번재 파티션보다 데이터 범위를 크게 입력 한다.

CREATE TABLE SCOTT.TEMP_PE_TEST
NOLOGGING
PARALLEL 4
AS
select SEQ from (SELECT ROWNUM SEQ FROM DUAL CONNECT BY ROWNUM <=3000)
where SEQ >= 2000;

ALTER TABLE SCOTT.TEMP_PE_TEST LOGGING NOPARALLEL;

■ TEMP_PE_TEST 테이블의 값의 범위는 아래와 같이 최대 3000, 최소 2000 로 입력 되어있다.

SELECT MAX(SEQ), MIN(SEQ) FROM SCOTT.TEMP_PE_TEST;

MAX(SEQ)  MIN(SEQ)
----------------------------
3000            2000

3. WITHOUT VALIDATION

■ WITHOUT VALIDATION 옵션을 사용하여 partition exchange(PE 로 지칭함) 를 한다.

ALTER TABLE SCOTT.PE_TEST EXCHANGE PARTITION TEST_S0003 WITH TABLE SCOTT.TEMP_PE_TEST WITHOUT VALIDATION; => 정상적으로 수행된다. 현재 TEMP_PE_TEST 테이블과 TEST_S0003 파티션이 교체가 된 상태임으로 데이터는 PE_TEST 에 존재 한다.
다시 한번 위의 문장을 수행해서 원래 대로 데이터가 TEMP_PE_TEST 에 존재 하도록 한다.

이번에는 WITHOUT VALIDATION 옵션 없이 실행 한다.
ALTER TABLE SCOTT.PE_TEST EXCHANGE PARTITION TEST_S0003 WITH TABLE SCOTT.TEMP_PE_TEST;


실행 하면 아래와 같이 ORA-14099 에러가 발생 한다.
1행에 오류:
ORA-14099: 테이블에 있는 모든 행이 지정된 분할 영역에 적합하지 않습니다.


■ 문장의 의미대로 WITH VALIDATION 은 PE 시 값의 범위의 검증을 하지 않는다 라는 의미 이며,검증 없이 딕셔너리 갱신만 이루어 짐으로 빠른 PE 가 가능하다.

■ 그러므로 옵션 없이(WITHOUT VALIDATION) PE 실행 시 Default 로 WITH VALIDATION 로 문장이 실행 됨으로 값의 범위에 대해서 검증을 하게 된다. 

■ WITHOUT VALIDATION 옵션 없이 10000000건(대략 120MB) 을 PE 진행시 필자 시스템에서 6~7초 가량 걸리 때문에 실제 대용량 테이블을 PE 하는데는 더욱 많은 시간이 걸리 것이다. 

4. INCLUDING INDEXES

■ 먼저 테스트를 위해서 위에서 생성한 테이블을 삭제 한다.

DROP TABLE SCOTT.PE_TEST PURGE;
DROP TABLE SCOTT.TEMP_PE_TEST PURGE; 

■ 테이블 생성 및 데이터를 입력, 이번에는 파티션 범위 내에 정상 데이터를 입력한다.

CREATE TABLE SCOTT.PE_TEST (SEQ NUMBER) PARTITION BY RANGE(SEQ)
( PARTITION TEST_S0001 VALUES LESS THAN (1000),
  PARTITION TEST_S0002 VALUES LESS THAN (2000),
  PARTITION TEST_S0003 VALUES LESS THAN (3000),
  PARTITION TEST_S0004 VALUES LESS THAN (4000));

 ALTER TABLE SCOTT.PE_TEST ENABLE ROW MOVEMENT; 

CREATE TABLE SCOTT.TEMP_PE_TEST
NOLOGGING
PARALLEL 4
AS
select SEQ from (SELECT ROWNUM SEQ FROM DUAL CONNECT BY ROWNUM < 3000)
where SEQ >= 2000;  

ALTER TABLE SCOTT.TEMP_PE_TEST LOGGING NOPARALLEL;

■ TEMP_PE_TEST 테이블의 값의 다시 확인 하면 아래와 같이 최대 2999, 최소 2000 로 입력 되어있다.

SELECT MAX(SEQ), MIN(SEQ) FROM SCOTT.TEMP_PE_TEST; 

MAX(SEQ)    MIN(SEQ)
------------------------------
   2999           2000

■ 인덱스 생성 - PE_TEST_PK 는 USERS 테이블 스페이스에, TEMP_PE_TEST_PK는 USERS2 에 생성한다.

CREATE UNIQUE INDEX SCOTT.PE_TEST_PK ON SCOTT.PE_TEST(SEQ)
TABLESPACE USERS
LOCAL;

CREATE UNIQUE INDEX SCOTT.TEMP_PE_TEST_PK ON SCOTT.TEMP_PE_TEST(SEQ)
TABLESPACE USERS2 ;

■ 제약조건을 추가한다.

ALTER TABLE SCOTT.PE_TEST ADD CONSTRAINT PE_TEST_PK  PRIMARY KEY(SEQ) using index SCOTT.PE_TEST_PK;

ALTER TABLE SCOTT.TEMP_PE_TEST ADD CONSTRAINT TEMP_PE_TEST_PK  PRIMARY KEY(SEQ) using index SCOTT.TEMP_PE_TEST_PK;

■ 현재 INDEX의 상태를 조회

--PARTITION INDEX

SELECT A.INDEX_OWNER,A.INDEX_NAME,B.UNIQUENESS,A.PARTITION_NAME,
A.STATUS,A.TABLESPACE_NAME 
FROM DBA_IND_PARTITIONS A, DBA_INDEXES B
WHERE A.INDEX_NAME=B.INDEX_NAME
AND A.INDEX_NAME IN (SELECT INDEX_NAME FROM DBA_INDEXES
WHERE TABLE_NAME IN ( 'PE_TEST' ))
ORDER BY A.INDEX_OWNER,A.INDEX_NAME,A.PARTITION_POSITION;

INDEX_OWNER  INDEX_NAME    UNIQUENESS  PARTITION_NAME  STATUS     TABLESPACE_NAME

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

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0001       USABLE                USERS

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0002       USABLE                USERS

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0003       USABLE                USERS

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0004       USABLE                USERS

-- NON PARTITION INDEX

SELECT A.OWNER, A.INDEX_NAME IND_NAME, A.TABLE_NAME TB_NAME,A.TABLESPACE_NAME TBS, A.UNIQUENESS UNI,A.PARTITIONED PART,
DECODE(B.CONSTRAINT_TYPE,'P','PK','NON PK') CON,A.STATUS
FROM DBA_INDEXES A, DBA_CONSTRAINTS B
WHERE A.INDEX_NAME=B.INDEX_NAME(+)
AND A.TABLE_NAME IN( 'TEMP_PE_TEST')
AND A.OWNER='SCOTT';

OWNER       IND_NAME       TB_NAME          TBS            UNI          PART      CON     STATUS      

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

SCOTT    TEMP_PE_TEST_PK    TEMP_PE_TEST    USERS2      UNIQUE      NO        PK      VALID

■ 먼저 WITHOUT VALIDATION 옵션을 사용하여 PE를 한다.

ALTER TABLE SCOTT.PE_TEST EXCHANGE PARTITION TEST_S0003 WITH TABLE SCOTT.TEMP_PE_TEST WITHOUT VALIDATION;

■ 문장 실행 후 다시 인덱스 상태를 조회 한다.

--PARTITION INDEX

INDEX_OWNER  INDEX_NAME    UNIQUENESS  PARTITION_NAME  STATUS   TABLESPACE_NAME

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

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0001       USABLE                 USERS

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0002       USABLE                 USERS

SCOTT              PE_TEST_PK       UNIQUE            TEST_S0003       UNUSABLE            USERS

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0004       USABLE                 USERS

-- NON PARTITION INDEX

OWNER       IND_NAME       TB_NAME          TBS           UNI         PART       CON      STATUS

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

SCOTT    TEMP_PE_TEST_PK    TEMP_PE_TEST   USERS2      UNIQUE      NO        PK    UNUSABLE

■ 위와 같이 2개의 인덱스 모두 UNUSABLE 상태로 되었다.  REBUILD 를 실행 한다.

ALTER INDEX SCOTT.TEMP_PE_TEST_PK REBUILD ONLINE;

ALTER INDEX SCOTT.PE_TEST_PK REBUILD PARTITION TEST_S0003 ONLINE;

■ 이번에는 INCLUDING INDEXES 옵션을 사용해서 PE를 진행한다.

ALTER TABLE SCOTT.PE_TEST EXCHANGE PARTITION TEST_S0003 WITH TABLE SCOTT.TEMP_PE_TEST INCLUDING INDEXES WITHOUT VALIDATION;

■ 다시 INDEX 의 상태를 조회 한다.

--PARTITION INDEX

INDEX_OWNER  INDEX_NAME    UNIQUENESS   PARTITION_NAME   STATUS     TABLESPACE_NAME

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

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0001       USABLE                USERS

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0002       USABLE                USERS

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0003       USABLE               USERS2

SCOTT              PE_TEST_PK       UNIQUE           TEST_S0004       USABLE                USERS

-- NON PARTITION INDEX

OWNER       IND_NAME       TB_NAME           TBS           UNI         PART       CON       STATUS

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

SCOTT    TEMP_PE_TEST_PK     TEMP_PE_TEST USERS     UNIQUE       NO       PK       USABLE

■ INCLUDING INDEXES 는 PE 진행시 INDEX도 같이 Exchange 를 하게 된다

■ 그래서 TABLESPACE 도 같이 변경 되며, INDEX 가 정상(USABLE) 상태인 것을 확인 할 수 있다.

■ 반대 옵션은 EXCLUDING INDEXES 이며, PE 시 INDEX에 관한 옵션을 지정하지 않으면 기본은 EXCLUDING INDEXES 이다. 그러므로 INDEX가 생성되어있다면, 사용불가(UNUSABLE) 상태가 되지 않도록 INCLUDING INDEXES 옵션을 사용 해야 할 것이다.

5. UPDATE GLOBAL INDXES

 ■ 대상 테이블에 GLOBAL INDEX가 생성 되어있다면 PE 를 실행 후 반드시 UNUSABLE 로 빠지게 된다.

■ GLOBAL INDEX에 대해서 REBUILD를 할 때 사용하는 것이 UPDATE GLOBAL INDEXES 옵션 이다.

■ 테이블 생성 및 데이터 입력

DROP TABLE SCOTT.PE_TEST PURGE;

DROP TABLE SCOTT.TEMP_PE_TEST PURGE;

CREATE TABLE SCOTT.PE_TEST (SEQ NUMBER) PARTITION BY RANGE(SEQ)
( PARTITION TEST_S0001 VALUES LESS THAN (1000),
  PARTITION TEST_S0002 VALUES LESS THAN (2000),
  PARTITION TEST_S0003 VALUES LESS THAN (3000),
  PARTITION TEST_S0004 VALUES LESS THAN (4000) );

 ALTER TABLE SCOTT.PE_TEST ENABLE ROW MOVEMENT;

CREATE TABLE SCOTT.TEMP_PE_TEST
NOLOGGING
PARALLEL 4
AS
select SEQ from (SELECT ROWNUM SEQ FROM DUAL CONNECT BY ROWNUM < 3000)
where SEQ >= 2000;

ALTER TABLE SCOTT.TEMP_PE_TEST LOGGING NOPARALLEL;

■ 인덱스 생성한다.

PE_TEST_PK 는 USERS 테이블 스페이스에, TEMP_PE_TEST_PK는 USERS2 에 생성한다.

PE_TEST_PK는 GLOBAL INDEX 로 생성한다.CREATE UNIQUE INDEX SCOTT.PE_TEST_PK ON SCOTT.PE_TEST(SEQ)
TABLESPACE USERS
GLOBAL;


CREATE UNIQUE INDEX SCOTT.TEMP_PE_TEST_PK ON SCOTT.TEMP_PE_TEST(SEQ)
TABLESPACE USERS2 ;

 

■ 제약조건을 추가한다.

ALTER TABLE SCOTT.PE_TEST ADD CONSTRAINT PE_TEST_PK  PRIMARY KEY(SEQ) using index SCOTT.PE_TEST_PK;

ALTER TABLE SCOTT.TEMP_PE_TEST ADD CONSTRAINT TEMP_PE_TEST_PK  PRIMARY KEY(SEQ) using index SCOTT.TEMP_PE_TEST_PK;

■ 먼저 INCLUDING INDEXES 옵션을 사용해 PE 를 실행 하면 아래와 같이 에러가 발생한다.

ORA-14098: ALTER TABLE EXCHANGE PARTITION에 테이블에 대한 인덱스가 일치하지 않습니다

■ WITHOUT VALIDATION 옵션만을 사용 후 INDEX의 상태를 조회 하면 아래와 같이 UNUSABLE 상태 임을 확인 할 수 있다. 

OWNER    IND_NAME             TB_NAME         TBS          UNI          PART   CON      STATUS

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

SCOTT    PE_TEST_PK              PE_TEST         USERS      UNIQUE       NO      PK       UNUSABLE

SCOTT    TEMP_PE_TEST_PK   TEMP_PE_TEST   USERS2     UNIQUE       NO      PK      UNUSABLE

■ INDEX REBUILD 후 UPDATE GLOBAL INDEXES 옵션을 사용해서 진행 한다.

ALTER INDEX SCOTT.TEMP_PE_TEST_PK REBUILD ONLINE;

ALTER INDEX SCOTT.PE_TEST_PK REBUILD ONLINE;

ALTER TABLE SCOTT.PE_TEST EXCHANGE PARTITION TEST_S0003 WITH TABLE SCOTT.TEMP_PE_TEST WITHOUT VALIDATION UPDATE GLOBAL INDEXES ;

 ■ 다시 INDEX 상태를 조회 한다.

OWNER    IND_NAME             TB_NAME         TBS          UNI          PART   CON      STATUS

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

SCOTT    PE_TEST_PK               PE_TEST       USERS      UNIQUE         NO     PK        USABLE

SCOTT    TEMP_PE_TEST_PK  TEMP_PE_TEST   USERS2     UNIQUE         NO     PK      UNUSABLE

■ 위에서 설명 한 것처럼 PE 실행 후 REBUILD 를 해준 것과 동일 하기 때문에 GLOBAL INDEX는 USABLE 상태이지만, 일반 INDEX는 UNSABLE 상태 가 되게 된다.

■ UPDATE GLOBAL INDEXES 옵션을 사용하게 되면, REBUILD의 DDL이 수행 되기 때문에 PE의 성능은 감소 하게 된다.

■ 테이블의 사이즈에 따라 PE 에 걸리는 시간은 증가 할 것이다. 그렇기 때문에 대용량 테이블에 GLOBAL INDEX 가 사용 중 일 때는 가용공간이 있다면 동일한 테이블 및 인덱스를 생성 후 임시 테이블과 원본테이블을 RENAME 하는 것이 빠를 수도 있을 것이다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io