- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949355&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
테이블 파티셔닝
- 파티셔닝은 테이블과 인덱스 데이터를 파티션(Partition) 단위로 나누어 저장하는 것을 말한다.
- 테이블을 파티셔닝하면 하나의 테이블일지라도 파티션키에 따라 물리적으로 별도의 세그먼트에 데이터가 저장되며, 인덱스도 마찬가지다.
- 관리적 측면 : 파티션 단이 백업, 추가, 삭제, 변경
- 성능적 측면 : 파티션 단위 조회 및 DML수행
파티션 기본 구조
수동 파티셔닝
-
- 오라클 8이전에는 파티션 뷰를 통해 파티션 기능을 구현했으며 이를 수동파티셔닝 이라고 한다.
-- 파티션 뷰를 정의할 때 사용할 Base 테이블을 만든다. SQL> create table p1 as select * from emp where deptno = 10; 테이블이 생성되었습니다. SQL> create table p2 as select * from emp where deptno = 20; 테이블이 생성되었습니다. SQL> create table p3 as select * from emp where deptno = 30; 테이블이 생성되었습니다. -- 체크 제약을 반드시 설정해야 함 SQL> alter table p1 add constraint c_deptno_10 check(deptno < 20); 테이블이 변경되었습니다. SQL> alter table p2 add constraint c_deptno_20 check(deptno >= 20 and deptno < 30); 테이블이 변경되었습니다. SQL> alter table p3 add constraint c_deptno_30 check(deptno >= 30 and deptno < 40); 테이블이 변경되었습니다. SQL> create index p1_empno_idx on p1(empno); 인덱스가 생성되었습니다. SQL> create index p2_empno_idx on p2(empno); 인덱스가 생성되었습니다. SQL> create index p3_empno_idx on p3(empno); 인덱스가 생성되었습니다. SQL> analyze table p1 compute statistics; 테이블이 분석되었습니다. SQL> analyze table p2 compute statistics; 테이블이 분석되었습니다. SQL> analyze table p3 compute statistics; 테이블이 분석되었습니다. -- 파티션 뷰를 정의한다. SQL> create or replace view partition_view 2 as 3 select * from p1 4 union all 5 select * from p2 6 union all 7 select * from p3 ; 뷰가 생성되었습니다. SQL> explain plan for 2 select * from partition_view 3 where deptno = :deptno ; 해석되었습니다. SQL> @?/rdbms/admin/utlxpls PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1236824284 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 170 | 4 (0)| 00:00:01 | | 1 | VIEW | PARTITION_VIEW | 5 | 170 | 4 (0)| 00:00:01 | | 2 | UNION-ALL PARTITION| | | | | | |* 3 | TABLE ACCESS FULL | P1 | 3 | 93 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL | P2 | 5 | 155 | 3 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | P3 | 6 | 204 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("DEPTNO"=TO_NUMBER(:DEPTNO)) 4 - filter("DEPTNO"=TO_NUMBER(:DEPTNO)) 5 - filter("DEPTNO"=TO_NUMBER(:DEPTNO)) 19 개의 행이 선택되었습니다. SQL> explain plan for 2 select * from partition_view 3 where deptno = 10; 해석되었습니다. SQL> @?/rdbms/admin/utlxpls PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 180990235 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 170 | 4 (0)| 00:00:01 | | 1 | VIEW | PARTITION_VIEW | 5 | 170 | 4 (0)| 00:00:01 | | 2 | UNION-ALL PARTITION| | | | | | |* 3 | TABLE ACCESS FULL | P1 | 3 | 93 | 3 (0)| 00:00:01 | |* 4 | FILTER | | | | | | |* 5 | TABLE ACCESS FULL| P2 | 1 | 31 | 3 (0)| 00:00:01 | |* 6 | FILTER | | | | | | |* 7 | TABLE ACCESS FULL| P3 | 1 | 34 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("DEPTNO"=10) 4 - filter(NULL IS NOT NULL) 5 - filter("DEPTNO"=10) 6 - filter(NULL IS NOT NULL) 7 - filter("DEPTNO"=10) 23 개의 행이 선택되었습니다.
파티션 테이블
SQL> create table partition_table 2 partition by range(deptno) ( 3 partition p1 values less than(20) 4 , partition p2 values less than(30) 5 , partition p3 values less than(40) 6 ) 7 as 8 select * from emp ; 테이블이 생성되었습니다. SQL> create index ptable_empno_idx on partition_table(empno) LOCAL; 인덱스가 생성되었습니다.
- 위의 파티션 테이블을 정의하면 세 개의 세그먼트가 생성된다.
- 인덱스 생성시도 LOCAL 옵션을 지정했으므로 각 파티션별로 개별적인 인덱스가 생성된다.
- 내부에 몇 개의 세그먼트를 생성하고 그것들이 논리적으로 하나의 오브젝트임을 메타정보로 딕셔너리에 저장해 두는 것에 지나지 않는다.
- 파티션 유형
Range 파티셔닝
- 오라클 8버전부터 제공된 가장 기초적인 파티셔닝 방식
-- Range 파티셔닝 create table 주문( 주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5) ) partition by range(주문일자) ( partition p2009_q1 values less than('20090401') , partition p2009_q2 values less than('20090701') , partition p2009_q3 values less than('20091001') , partition p2009_q4 values less than('20100101') , partition p2010_q1 values less than('20100401') , partition p9999_mx values less than( MAXVALUE ) );
- 위와 같이 파티셔닝 테이블에 값을 입력하면 각 레코드를 파티션 키 컬럼 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 읽을 수 있어 이력성 데이터 조회 시 성능을 크게 향상시켜 준다.
- 파티션 키로는 하나 이상의 컬럼을 지정할 수 있고, 최대 16개 까지 허용된다.
- 보관 주기 정책에 따라 과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터관리 작업을 효율적이고 빠르게 수행할 수 있는 것도 큰 장점이다.
- DB관리자의 실수로 신규 파티션 생성을 빠뜨리면 월초 또는 연초에 데이터가 입력되지 않는 에러가 발생하므로, maxvalue 파티션을 반드시 ㅅ생성해 두는 것이 좋다.
- 11g 부터는 Range 파티션을 생성할 때 interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동추가 되도록 할 수 있다.
해시 파티셔닝
- 해시파티셔닝은 Range 파티셔닝에 이어 오라클 8i 버전부터 제공
- 파티션 키에 해시함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식
- 검색할 때는 조건절 비교값에 해시 함수를 적용해 읽어야 할 파티션을 결정하며, 해시 알고리즘 특성상 "=" 조건 또는 "In-List" 조건으로 검색할 때만 파티션 Pruning이 작동한다.
SQL> create table t_hash1 2 (c1 number, c2 varchar2(10)) 3 partition by hash (c1) 4 (partition p1 tablespace users, 5 partition p2 tablespace users); 테이블이 생성되었습니다. SQL> create index t_hash1_idx on t_hash1(c1) local; 인덱스가 생성되었습니다. SQL> select index_name, status 2 from dba_ind_partitions 3 where index_name = 'T_HASH1_IDX'; INDEX_NAME STATUS ------------------------------ -------- T_HASH1_IDX USABLE T_HASH1_IDX USABLE SQL> insert into t_hash1 2 select level, 'a' from dual connect by level<=1000; 1000 개의 행이 만들어졌습니다. SQL> commit; SQL> select index_name, status 2 from dba_ind_partitions 3 where index_name = 'T_HASH1_IDX'; INDEX_NAME STATUS ------------------------------ -------- T_HASH1_IDX USABLE T_HASH1_IDX USABLE SQL> alter table t_hash1 2 add partition p3 tablespace users; 테이블이 변경되었습니다. SQL> select table_name, tablespace_name from dba_tab_partitions 2 where table_name = 'T_HASH1'; TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ T_HASH1 USERS T_HASH1 USERS T_HASH1 USERS SQL> alter table t_hash1 2 add partition p3 tablespace users; 테이블이 변경되었습니다. SQL> select table_name, partition_name from dba_tab_partitions 2 where table_name = 'T_HASH1'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ T_HASH1 P1 T_HASH1 P2 T_HASH1 P3 SQL> select index_name, status 2 from dba_ind_partitions 3 where index_name = 'T_HASH1_IDX'; INDEX_NAME STATUS ------------------------------ -------- T_HASH1_IDX UNUSABLE T_HASH1_IDX USABLE T_HASH1_IDX UNUSABLE SQL> alter table t_hash1 2 add partition p4 tablespace users; 테이블이 변경되었습니다. SQL> select index_name, status 2 from dba_ind_partitions 3 where index_name = 'T_HASH1_IDX'; INDEX_NAME STATUS ------------------------------ -------- T_HASH1_IDX UNUSABLE T_HASH1_IDX UNUSABLE T_HASH1_IDX UNUSABLE T_HASH1_IDX UNUSABLE
- 테이블 파티셔닝 여부를 결정할 대는 데이터가 얼마나 고르게 분산될 수 있느냐가 가장 중요한 관심사항이다.
- 해시 파티셔닝 할 때 특히 데이터 분포를 신중히 고려해야 하는데, 사용자가 직접 파티션 기준을 정하는 Range, 리스트 파티셔닝과 다르게 해시 파티셔닝은 파티션 개수만 사용자가 결정하고 데이터를 분사시키는 해싱 알고리즘은 오라클에 으해 결정되기 때문이다.
- 파티션 키를 잘못 선정하면 데이터가 고르게 분산되지 않아 파티셔닝의 이점이 사라질 수도 있다.
- 오라클은, 특정 파티션에 데이터가 물리지 않도록 하려면 파티션 개수를 2의 제곱으로 설정할 것을 권고한다.
- Linear hashing Algorithm 을 사용하지 때문에 2의 제곱으로 설정할 것으로 권고한다.
- Linear Hashing Alogorithm의 주소 확장방법은 한번에 모든 버킷을 분할하지 않고 첫 번째 버킷부터 분할해 가면서 마지막 버킷 분할이 끝나면 처음 버킷 에서부터 다시 시작하는 싸이클 방식 임 .
병렬 쿼리 성능 향상
데이터가 모든 파티션에 고르게 분산돼 있다면 더구나 각 파티션이 서로 다른 디바이스에 저장돼 있다면 병렬 I/O 성능을 극대화 할 수 있다.
DML 경합 분산
- 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로 해시 파티셔닝을 사용한다.
- 데이터가 입력되는 테이블 블록에도 경합이 발생할 수 이씨만, 그보다는 입력할 블록을 할당받기 위한 Freelist 조회 때문에 세그먼트 헤더 블록에 대한 경합이 발생 할 때 해시 파티셔닝 하면 헤더 블록에 대한 경합을 줄일 수 있다.
- Right Growing 인데그도 자주 경합 지점이 되곤 하는데, 맨 우측 끝 블록에만 값이 입력되는 특징때문이므로 이때 인덱스를 해시 파티셔닝함으로써 경합 발생 가능성을 낮출 수 있다.
(4) 리스트 파티셔닝
- Oracle 9i 버전부터 제공되었으며, 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다.
create table 인터넷매물( 물건코드varchar2(5), 지역?류varchar2(4) )
partition by list(지역?류)
(partition p_지역1 values ('서울'),
partition p_지역2 values ('경기', '인천'),
partition p_지역3 values ('부산', '대구', '대전', '광주'),
partition p_기타values (DEFAULT) ) ;
- 순서와 상관없이 불연속적인 값의 목록으로써 결정된다.
- 단일 컬럼으로만 파티션 키를 지정해야 한다.
- 리스트 파티션에도 default 파티션을 생성해 두어야 안전하다.
(5) 결합 파티셔닝
- 결합 파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고 서브 파티션 단위로 데이터를 저장한다.
- 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정한다.
Range + 해시 결합 파티셔닝
create table 주문( 주문번호number, 주문일자varchar2(8), 고객id varchar2(5) )
partition by range(주문일자)
subpartitionby hash(고객id) subpartitions8
( partition p2009_q1 values less than('20090401'),
partition p2009_q2 values less than('20090701'),
partition p2009_q3 values less than('20091001'),
partition p2009_q4 values less than('20100101'),
partition p2010_q1 values less than('20100401'),
partition p9999_mx values less than( MAXVALUE ));
Range + 리스트 결합 파티셔닝
create table 판매 ( 판매점 varchar2(10), 판매일자 varchar2(8) ) partition by range(판매일자) subpartition by list(판매점) subpartition template ( subpartition lst_01 values ('강남지점', '강북지점', '강서지점', '강동지점') , subpartition lst_02 values ('부산지점', '대전지점') , subpartition lst_03 values ('인천지점', '제주지점', '의정부지점') , subpartition lst_99 values ( DEFAULT ) ) ( partition p2009_q1 values less than('20090401') , partition p2009_q2 values less than('20090701') , partition p2009_q3 values less than('20091001') , partition p2009_q4 values less than('20100101') );
기타 결합 파티셔닝
- Range-Range
- 리스트-해시
- 리스트-리스트
- 리스트-Range
11g에 추가된 파티션 유형
- 상품 테이블을 상품댑ㄴ류 기준으로 리스트 파티셔닝하고, 일별상품거래 테이블도 부모 테이블인 상품과 똑 같은 방식과 기준으로 파티셔닝하려고 한다.
- 이럴 때 10g 까지는 상품에 있는 상품대분류 컬럼을 일별 상품거래 테이블에 반정규화 해야만 했다.
- 11g에서 부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝하는 기능이 도입되었는데, 이를 'Reference 파티션' 이라고 부른다.
Create table 상품 { 상품번호 numberNOT NULL PRIMARY KEY , 상품명 varchar2(50) not null , 현재가격 number not null , 상품대분류 varchar2(4) not null , 등록일시 date not null ) Partition by list(상품대분류) ( Partition p1 values ('의류') ,partition p2 values ('식품') ,partition p3 values ('가전') ,partition 4 values ('컴퓨터') ); create table 일별상품거래 ( 상품번호 number NOT NULL , 거래일자 varchar2(8) , 판매가격 number , 판매수량 number , 판매금액 number , constraint 일별상품거래_fk foreign key(상품번호) references 상품 ) partition by reference (일별상품거래_fk);
Interval 파티셔닝
- 11g 부터는 Range 파티션을 생성할 대 아래와 같이 interval, 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.
create table 주문일자 (주문번호 number, 주문일시 date, ... ) partition by range(주문일시) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( , partition p200907 values less than(to_date('2009/08/01', 'yyyy/mm/dd')) , partition p200908 values less than(to_date('2009/09/01', 'yyyy/mm/dd')) , partition p200909 values less than(to_date('2009/10/01', 'yyyy/mm/dd')) , partition p200910 values less than(to_date('2009/11/01', 'yyyy/mm/dd')) ); create table 고객 (고객번호 number, 고객명 varchar2(20), ... ) partition by range(고객번호) INTERVAL (100000) ( partition p_cust1 values less than ( 100001 ) , partition p_cust2 values less than ( 200001 ) , partition p_cust3 values less than ( 300001 ) ) ;
문서에 대하여
최초작성자 : 이가혜
최초작성일 : 2010년 06월 11일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=4949355&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.