1. TEST Information
|
Items |
Description |
|
Test Date |
2012 / 01 / 15 |
|
CPU |
VirtualBox VCPUx4 |
|
Main Memory |
2GB |
|
O/S version |
RHEL 5.5 |
|
Host Name |
test1, test2 |
|
ORACLE_SID |
testdb |
|
Oracle version |
10.2.0.5 |
2. Scenario&Environment
1) exchange partition option 에 대해서 상황별 사용 예시
3. Test Table Creation
■ 파티션 테이블 생성
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
4. 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 하는데는 더욱 많은 시간이 걸리 것이다.
5. 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 옵션을 사용 해야 할 것이다.
6. UPDATE GLOBAL INDEXES
■ 대상 테이블에 GLOBAL INDEX가 생성 되어있다면 PE 를 실행 후 반드시 UNUSABLE 로 빠지게 된다.
PE를 진행하면서 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 하는 것이 빠를 수도
있을 것이다.
파이어폭스에 최적화 되어있습니다
이 포스트가 유용하셨다면 구독하세요
RAC_Upgrade_11.2.0.2_to_11.2.0.3.pdf
0