I. 테스트 환경 구성

1. 스키마 생성 및 권한 부여

테스트 스키마 생성 및 권한 부여
create tablesapce PT_TEST datafile '+DATA' size 3G;
create user TEST identified by TEST default tablespace PT_TEST;
grant CONNECT, RESOURCE, UNLIMITED TABLESPACE to TEST;

2. 테스트용 원본 테이블 생성

A) 원본 테이블 생성

테스트용 원본 테이블 생성
create table TEST.EXTEST
(
    BASE_DT     varchar2(8) not null,
    TESTCOL1    varchar2(20) not null,
    TESTCOL2    number not null,
    TESTCOL3    char(20) not null,
    TESTCOL4    varchar2(20),
    TESTCOL5    char(20),
    TESTCOL6    nchar(20),
    TESTCOL7    varchar2(20),
    TESTCOL8    varchar2(20),
    TESTCOL9    varchar2(20),
    constraint PK_EXTEST primary key (BASE_DT,TESTCOL1,TESTCOL2)
)
tablespace USERS; -- 초기 데이터는 USERS에 적재

B) 랜덤 데이터 입력

랜덤 데이터 입력
insert
  into TEST.EXTEST
select to_char(sysdate-dbms_random.value(0,365*7),'RRRRMMDD') as BASE_DT,
       dbms_random.string('A',20) as TEST_COL1,
       dbms_random.value(0,1000)  as TEST_COL2,
       dbms_random.string('A',20) as TEST_COL3,
       dbms_random.string('A',20) as TEST_COL4,
       dbms_random.string('A',20) as TEST_COL5,
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 2
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 4
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 6
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 8
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 10
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 12
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 14
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 16
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 18
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) as TEST_COL6, --20
       dbms_random.string('A',20) as TEST_COL7,
	   dbms_random.string('A',2) ||                                                      -- 2
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 8
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) || -- 14
       NCHR(dbms_random.value(44032, 55203)) || NCHR(dbms_random.value(44032, 55203)) as TEST_COL8, --20
       dbms_random.string('A',20) as TEST_COL9
  from DUAL connect by level <= 5000000; -- 메모리 부족 발생시 1000000건으로 5번 수행

II. 테이블 파티셔닝

1. 파티션 테이블 생성

A) 세션 병렬 처리 설정

세션 병렬 처리 설정
alter session enable parallel ddl;
alter session enable parallel dml;
alter session force parallel dml parallel 8;
alter session force parallel ddl parallel 8;
alter session force parallel query parallel 8;
-- cpu_count 파라미터보다 1 적은 숫자로 입력 (최대값)

B) 테이블 생성

파티션 테이블 생성
create table TEST.EXTEST_PT
(
    BASE_DT     varchar2(8) not null,
    TESTCOL1    varchar2(20) not null,
    TESTCOL2    number not null,
    TESTCOL3    char(20) not null,
    TESTCOL4    varchar2(20),
    TESTCOL5    char(20),
    TESTCOL6    nchar(20),
    TESTCOL7    varchar2(20),
    TESTCOL8    varchar2(20),
    TESTCOL9    varchar2(20)
)
tablespace PT_TEST
-- nologging ==> 데이터를 적재하는 작업이 아닌 segment를 교환하는 작업이므로 nologging 옵션이 필요하지는 않음
partition by range (BASE_DT)
(
    partition EXTEST_PT_P1 values less than ('20250101')
);
-- 단일 파티션을 생성하여 원본 테이블과 교체한 후에 분할하는 방식임
-- exchange 시 1개 파티션과 1개 테이블이 1대 1로 교체될 뿐, 키 값에 따라 다른 파티션에 데이터가 배포되지는 않음

C) 인덱스 및 제약조건 생성

PK 인덱스 생성
create unique index TEST.PK_EXTEST_PT
on TEST.EXTEST_PT (BASE_DT,TESTCOL1,TESTCOL2)
local
tablespace PT_TEST;


PK 제약조건 생성
alter table TEST.EXTEST_PT add constraint PK_EXTEST_PT primary key (BASE_DT,TESTCOL1,TESTCOL2) using index;

2. 파티션 교환

파티션 Exchange
alter table TEST.EXTEST_PT
exchange partition EXTEST_PT_P1 with table TEST.EXTEST
including indexes;

3. 파티션 분할

파티션 Split 
alter table TEST.EXTEST_PT split partition EXTEST_PT_P1 into (
partition EXTEST_PT_P1505 values less than ('20150601'),
partition EXTEST_PT_P1506 values less than ('20150701'),
partition EXTEST_PT_P1507 values less than ('20150801'),
partition EXTEST_PT_P1508 values less than ('20150901'),
partition EXTEST_PT_P1509 values less than ('20151001'),
partition EXTEST_PT_P1510 values less than ('20151101'),
partition EXTEST_PT_P1511 values less than ('20151201'),
partition EXTEST_PT_P1512 values less than ('20160101'),
partition EXTEST_PT_P1601 values less than ('20160201'),
partition EXTEST_PT_P1602 values less than ('20160301'),
partition EXTEST_PT_P1603 values less than ('20160401'),
partition EXTEST_PT_P1604 values less than ('20160501'),
partition EXTEST_PT_P1605 values less than ('20160601'),
partition EXTEST_PT_P1606 values less than ('20160701'),
partition EXTEST_PT_P1607 values less than ('20160801'),
partition EXTEST_PT_P1608 values less than ('20160901'),
partition EXTEST_PT_P1609 values less than ('20161001'),
partition EXTEST_PT_P1610 values less than ('20161101'),
partition EXTEST_PT_P1611 values less than ('20161201'),
partition EXTEST_PT_P1612 values less than ('20170101'),
partition EXTEST_PT_P1701 values less than ('20170201'),
partition EXTEST_PT_P1702 values less than ('20170301'),
partition EXTEST_PT_P1703 values less than ('20170401'),
partition EXTEST_PT_P1704 values less than ('20170501'),
partition EXTEST_PT_P1705 values less than ('20170601'),
partition EXTEST_PT_P1706 values less than ('20170701'),
partition EXTEST_PT_P1707 values less than ('20170801'),
partition EXTEST_PT_P1708 values less than ('20170901'),
partition EXTEST_PT_P1709 values less than ('20171001'),
partition EXTEST_PT_P1710 values less than ('20171101'),
partition EXTEST_PT_P1711 values less than ('20171201'),
partition EXTEST_PT_P1712 values less than ('20180101'),
partition EXTEST_PT_P1801 values less than ('20180201'),
partition EXTEST_PT_P1802 values less than ('20180301'),
partition EXTEST_PT_P1803 values less than ('20180401'),
partition EXTEST_PT_P1804 values less than ('20180501'),
partition EXTEST_PT_P1805 values less than ('20180601'),
partition EXTEST_PT_P1806 values less than ('20180701'),
partition EXTEST_PT_P1807 values less than ('20180801'),
partition EXTEST_PT_P1808 values less than ('20180901'),
partition EXTEST_PT_P1809 values less than ('20181001'),
partition EXTEST_PT_P1810 values less than ('20181101'),
partition EXTEST_PT_P1811 values less than ('20181201'),
partition EXTEST_PT_P1812 values less than ('20190101'),
partition EXTEST_PT_P1901 values less than ('20190201'),
partition EXTEST_PT_P1902 values less than ('20190301'),
partition EXTEST_PT_P1903 values less than ('20190401'),
partition EXTEST_PT_P1904 values less than ('20190501'),
partition EXTEST_PT_P1905 values less than ('20190601'),
partition EXTEST_PT_P1906 values less than ('20190701'),
partition EXTEST_PT_P1907 values less than ('20190801'),
partition EXTEST_PT_P1908 values less than ('20190901'),
partition EXTEST_PT_P1909 values less than ('20191001'),
partition EXTEST_PT_P1910 values less than ('20191101'),
partition EXTEST_PT_P1911 values less than ('20191201'),
partition EXTEST_PT_P1912 values less than ('20200101'),
partition EXTEST_PT_P2001 values less than ('20200201'),
partition EXTEST_PT_P2002 values less than ('20200301'),
partition EXTEST_PT_P2003 values less than ('20200401'),
partition EXTEST_PT_P2004 values less than ('20200501'),
partition EXTEST_PT_P2005 values less than ('20200601'),
partition EXTEST_PT_P2006 values less than ('20200701'),
partition EXTEST_PT_P2007 values less than ('20200801'),
partition EXTEST_PT_P2008 values less than ('20200901'),
partition EXTEST_PT_P2009 values less than ('20201001'),
partition EXTEST_PT_P2010 values less than ('20201101'),
partition EXTEST_PT_P2011 values less than ('20201201'),
partition EXTEST_PT_P2012 values less than ('20210101'),
partition EXTEST_PT_P2101 values less than ('20210201'),
partition EXTEST_PT_P2102 values less than ('20210301'),
partition EXTEST_PT_P2103 values less than ('20210401'),
partition EXTEST_PT_P2104 values less than ('20210501'),
partition EXTEST_PT_P2105 values less than ('20210601'),
partition EXTEST_PT_P2106 values less than ('20210701'),
partition EXTEST_PT_P2107 values less than ('20210801'),
partition EXTEST_PT_P2108 values less than ('20210901'),
partition EXTEST_PT_P2109 values less than ('20211001'),
partition EXTEST_PT_P2110 values less than ('20211101'),
partition EXTEST_PT_P2111 values less than ('20211201'),
partition EXTEST_PT_P2112 values less than ('20220101'),
partition EXTEST_PT_P2201 values less than ('20220201'),
partition EXTEST_PT_P2202 values less than ('20220301'),
partition EXTEST_PT_P2203 values less than ('20220401'),
partition EXTEST_PT_P2204 values less than ('20220501'),
partition EXTEST_PT_P2205 values less than ('20220601'),
partition EXTEST_PT_P2206 values less than ('20220701'),
partition EXTEST_PT_P2207 values less than ('20220801'),
partition EXTEST_PT_P2208 values less than ('20220901'),
partition EXTEST_PT_P2209 values less than ('20221001'),
partition EXTEST_PT_P2210 values less than ('20221101'),
partition EXTEST_PT_P2211 values less than ('20221201'),
partition EXTEST_PT_P2212 values less than ('20230101'),
partition EXTEST_PT_P2301 values less than ('20230201'),
partition EXTEST_PT_P2302 values less than ('20230301'),
partition EXTEST_PT_P2303 values less than ('20230401'),
partition EXTEST_PT_P2304 values less than ('20230501'),
partition EXTEST_PT_P2305 values less than ('20230601'),
partition EXTEST_PT_P2306 values less than ('20230701'),
partition EXTEST_PT_P2307 values less than ('20230801'),
partition EXTEST_PT_P2308 values less than ('20230901'),
partition EXTEST_PT_P2309 values less than ('20231001'),
partition EXTEST_PT_P2310 values less than ('20231101'),
partition EXTEST_PT_P2311 values less than ('20231201'),
partition EXTEST_PT_P2312 values less than ('20240101'),
partition EXTEST_PT_P2401) update indexes;
-- update indexes 없이 split을 수행할 경우, 해당 파티션과 관련된 모든 인덱스가 UNUSABLE 상태로 되며 작업이 종료됨

4. 이름 변경

A) 테이블 이름 변경

테이블명 변경
drop table TEST.EXTEST; -- 빈 파티션에서 나온 빈 테이블
alter table TEST.EXTEST_PT rename to EXTEST;

B) 인덱스 및 제약조건 이름 변경

PK 제약조건 이름 변경
alter table TEST.EXTEST rename constraint PK_EXTEST_PT to PK_EXTEST;
alter index TEST.PK_EXTEST_PT rename to PK_EXTEST;


기술 메모

  1. 원본 테이블과 파티션 테이블의 인덱스가 일치하지 않을 경우, ORA-14098 에러가 발생되며 exchange가 진행되지 않음
    : 반드시 동일한 구조의 인덱스를 파티션에도 만들어 두어야 함
    : local 파티션 인덱스 생성 권장
  2. Split 수행시 분할된 파티션을 만들어 데이터를 내부적으로 적재하는 과정에서 테이블스페이스의 공간을 사용함
    : 원본 테이블만큼의 테이블스페이스 내의 빈 용량이 필요함
  3. Partition Split을 사용하는 방식이므로 범위(range) 및 리스트(list) 파티션에 사용함


  • 레이블 없음