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) 세션 병렬 처리 설정

세션 병렬 처리 설정
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.OPTEST (
    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_OPTEST primary key (BASE_DT,TESTCOL1,TESTCOL2)
)
tablespace USERS; -- 초기 데이터는 USERS에 적재

C) 랜덤 데이터 입력

랜덤 데이터 입력
insert
  into TEST.OPTEST 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. 테이블 파티셔닝

alter table modify 수행 
alter table TEST.OPTEST modify
partition by range (BASE_DT) 
(
partition OPTEST_P201504 values less than ('20150501'),
partition OPTEST_P201505 values less than ('20150601'),
partition OPTEST_P201506 values less than ('20150701'),
partition OPTEST_P201507 values less than ('20150801'),
partition OPTEST_P201508 values less than ('20150901'),
partition OPTEST_P201509 values less than ('20151001'),
partition OPTEST_P201510 values less than ('20151101'),
partition OPTEST_P201511 values less than ('20151201'),
partition OPTEST_P201512 values less than ('20160101'),
partition OPTEST_P201601 values less than ('20160201'),
partition OPTEST_P201602 values less than ('20160301'),
partition OPTEST_P201603 values less than ('20160401'),
partition OPTEST_P201604 values less than ('20160501'),
partition OPTEST_P201605 values less than ('20160601'),
partition OPTEST_P201606 values less than ('20160701'),
partition OPTEST_P201607 values less than ('20160801'),
partition OPTEST_P201608 values less than ('20160901'),
partition OPTEST_P201609 values less than ('20161001'),
partition OPTEST_P201610 values less than ('20161101'),
partition OPTEST_P201611 values less than ('20161201'),
partition OPTEST_P201612 values less than ('20170101'),
partition OPTEST_P201701 values less than ('20170201'),
partition OPTEST_P201702 values less than ('20170301'),
partition OPTEST_P201703 values less than ('20170401'),
partition OPTEST_P201704 values less than ('20170501'),
partition OPTEST_P201705 values less than ('20170601'),
partition OPTEST_P201706 values less than ('20170701'),
partition OPTEST_P201707 values less than ('20170801'),
partition OPTEST_P201708 values less than ('20170901'),
partition OPTEST_P201709 values less than ('20171001'),
partition OPTEST_P201710 values less than ('20171101'),
partition OPTEST_P201711 values less than ('20171201'),
partition OPTEST_P201712 values less than ('20180101'),
partition OPTEST_P201801 values less than ('20180201'),
partition OPTEST_P201802 values less than ('20180301'),
partition OPTEST_P201803 values less than ('20180401'),
partition OPTEST_P201804 values less than ('20180501'),
partition OPTEST_P201805 values less than ('20180601'),
partition OPTEST_P201806 values less than ('20180701'),
partition OPTEST_P201807 values less than ('20180801'),
partition OPTEST_P201808 values less than ('20180901'),
partition OPTEST_P201809 values less than ('20181001'),
partition OPTEST_P201810 values less than ('20181101'),
partition OPTEST_P201811 values less than ('20181201'),
partition OPTEST_P201812 values less than ('20190101'),
partition OPTEST_P201901 values less than ('20190201'),
partition OPTEST_P201902 values less than ('20190301'),
partition OPTEST_P201903 values less than ('20190401'),
partition OPTEST_P201904 values less than ('20190501'),
partition OPTEST_P201905 values less than ('20190601'),
partition OPTEST_P201906 values less than ('20190701'),
partition OPTEST_P201907 values less than ('20190801'),
partition OPTEST_P201908 values less than ('20190901'),
partition OPTEST_P201909 values less than ('20191001'),
partition OPTEST_P201910 values less than ('20191101'),
partition OPTEST_P201911 values less than ('20191201'),
partition OPTEST_P201912 values less than ('20200101'),
partition OPTEST_P202001 values less than ('20200201'),
partition OPTEST_P202002 values less than ('20200301'),
partition OPTEST_P202003 values less than ('20200401'),
partition OPTEST_P202004 values less than ('20200501'),
partition OPTEST_P202005 values less than ('20200601'),
partition OPTEST_P202006 values less than ('20200701'),
partition OPTEST_P202007 values less than ('20200801'),
partition OPTEST_P202008 values less than ('20200901'),
partition OPTEST_P202009 values less than ('20201001'),
partition OPTEST_P202010 values less than ('20201101'),
partition OPTEST_P202011 values less than ('20201201'),
partition OPTEST_P202012 values less than ('20210101'),
partition OPTEST_P202101 values less than ('20210201'),
partition OPTEST_P202102 values less than ('20210301'),
partition OPTEST_P202103 values less than ('20210401'),
partition OPTEST_P202104 values less than ('20210501'),
partition OPTEST_P202105 values less than ('20210601'),
partition OPTEST_P202106 values less than ('20210701'),
partition OPTEST_P202107 values less than ('20210801'),
partition OPTEST_P202108 values less than ('20210901'),
partition OPTEST_P202109 values less than ('20211001'),
partition OPTEST_P202110 values less than ('20211101'),
partition OPTEST_P202111 values less than ('20211201'),
partition OPTEST_P202112 values less than ('20220101'),
partition OPTEST_P202201 values less than ('20220201'),
partition OPTEST_P202202 values less than ('20220301'),
partition OPTEST_P202203 values less than ('20220401'),
partition OPTEST_P202204 values less than ('20220501'),
partition OPTEST_P202205 values less than ('20220601'),
partition OPTEST_P202206 values less than ('20220701'),
partition OPTEST_P202207 values less than ('20220801'),
partition OPTEST_P202208 values less than ('20220901'),
partition OPTEST_P202209 values less than ('20221001'),
partition OPTEST_P202210 values less than ('20221101'),
partition OPTEST_P202211 values less than ('20221201'),
partition OPTEST_P202212 values less than ('20230101')
) online;


기술 메모

  1. 12c부터 지원되며, 다른 작업에 비해 후속 작업이 적은 방식
    : 기존 테이블의 구조를 바꾸는 것이므로, rename하거나 제약조건이나 종속된 객체를 복사하는 작업이 없음
  2. 파티션 키 컬럼이 선투 컬럼인 인덱스는 자동으로 pre-fixed local 파티션 인덱스로 변환됨
    : 파티션 키 컬럼 포함되지 않거나, 선두 컬럼이 아닌 인덱스는 drop 후 재생성하는 방식으로 local 파티션 인덱스로 변환해야 함
  3. 파티션 세그먼트를 할당하고 기존의 데이터를 이동하는 방식이므로 테이블 크기 만큼의 공간이 필요함
    : 데이터 펌프를 제외한 모든 방식에 테이블스페이스 내의 추가 공간이 필요함
    : 용량이 부족할 경우 ORA-01652 에러가 발생하며 작업 취소됨 (인덱스 파티셔닝 과정 중에 발생할 경우에도, 전체 작업이 취소됨)


  • 레이블 없음