메뉴 건너뛰기

bysql.net

1. 테이블 파티셔닝

2011.06.21 05:38

오라클잭 조회 수:28261

*파티셔닝(partitioning)이란?

-테이블과 인덱스 데이터를 파티션단위로 나누어 저장하는 것을 말한다. 파티셔닝하면 하나의 테이블일지라도 파티션 키에 따라

 물리적으로 별도의 세그먼트에 데이터가 저장됨, 인덱스도

 

  • 관리적 측면 : 파티션 단위 백업,추가,삭제,변경
  • 성능적 측면 : 파티션 단위 조회 및  DML 수행

 

(1)파티션 기본 구조

  <수동파티셔닝>

 -파티션 뷰를 통해 파티션 기능을 구현

 

 

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

-- 파티션 뷰를 정의할 때 사용할 base 테이블 생성
create table p1 as select * from emp where deptno = 10;
create table p2 as select * from emp where deptno = 20;
create table p3 as select * from emp where deptno = 30;

 

-- 체크 제약을 반드시 설정해야 함
alter table p1 add constraint c_deptno_10 check(deptno < 20);
alter table p2 add constraint c_deptno_20 check(deptno >= 20 and deptno < 30);
alter table p3 add constraint c_deptno_30 check(deptno >= 30 and deptno < 40);

 

create index p1_empno_idx on p1(empno);
create index p2_empno_idx on p2(empno);
create index p3_empno_idx on p3(empno);

 

--파티션 뷰를 정의

create or replace view partition_view

as

select * from p1

union all

select * from p2

union all

select * from p3;

 

select * from partition_view
where deptno=10
and empno=7839;

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT               |                |        |       |     2 (100)|          |
|     1 |  VIEW                          | PARTITION_VIEW |      1 |    87 |     2   (0)| 00:00:01 |
|     2 |   UNION-ALL PARTITION          |                |        |       |            |          |
|*   3 |      TABLE ACCESS BY INDEX ROWID | P1             |      1 |    39 |     2   (0)| 00:00:01 |
|*   4 |          INDEX RANGE SCAN           | P1_EMPNO_IDX   |      1 |       |     1   (0)| 00:00:01 |
|*   5 |    FILTER                      |                |        |       |            |          |
|*   6 |       TABLE ACCESS BY INDEX ROWID| P2             |      1 |    39 |     2   (0)| 00:00:01 |
|*   7 |          INDEX RANGE SCAN          | P2_EMPNO_IDX   |      1 |       |     1   (0)| 00:00:01 |
|*   8 |    FILTER                      |                |        |       |            |          |
|*   9 |       TABLE ACCESS BY INDEX ROWID| P3             |      1 |    42 |     2   (0)| 00:00:01 |
|* 10 |          INDEX RANGE SCAN          | P3_EMPNO_IDX   |      1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

 

   3 - filter("DEPTNO"=10)
   4 - access("EMPNO"=7839)
   5 - filter(NULL IS NOT NULL)
   6 - filter("DEPTNO"=10)
   7 - access("EMPNO"=7839)
   8 - filter(NULL IS NOT NULL)
   9 - filter("DEPTNO"=10)
  10 - access("EMPNO"=7839)

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

 

<파티션 테이블>

오라클 8부터 도입

 

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

create table partition_table
partition by range(deptno) (
partition p1 values less than(20),
partition p2 values less than(30),
partition p3 values less than(40)
)
as
select * from emp ;

 

// partition by 절은 파티션 뷰의 base테이블에 체크 제약을 설정하는 것과 같은 역할

 

create index ptable_empno_idx on partition_table(empno) LOCAL;

//local 옵션 지정으로 각 파티션별로 개별적인 인덱스가 만들어짐

 

 

select * from partition_table
where deptno=10
and empno=7839;

 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  PARTITION RANGE SINGLE PARTITION: 1 1 (cr=4 pr=0 pw=0 time=275 us)
      1   TABLE ACCESS FULL PARTITION_TABLE PARTITION: 1 1 (cr=4 pr=0 pw=0 time=239 us)

 

 <버전 UP에 대한 파티션 기능 확장>

 *oracle8

  • range 파티션

 

*oracle8i

  • hash 파티션
  • range-hash 파티션

 

 *oracle9i

  • list 파티션
  • range-list 파티션

 

*oracle11g

  • range-range 파티션
  • list-hash 파티션
  • list-list 파티션
  • list-range 파티션

 

 (2)range 파티션

-날짜 컬럼을 기준으로 함

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

<ex>

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 )
);

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

1.파티션 키 컬럼 값에 따라 분할 저장,읽을 때도 검색 조건을 만족하는 파티션만 읽을 수 있어 이력성 데이터 조회시 성능 향상

2.보관 주기 정책에 따라 과거 파티션만 백업하고 삭제하는등의 관리 작업이 효율적

3.신규 파티션 생성이 안되면 해당 데이터 입력시 error 발생. maxvalue 파티션을 반드시 생성

 

(3)hash 파티션

-파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장

-고객 ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 선정해야 효과적

-등치 조건이나 IN-list조건으로 검색할 때만 파티션 pruning이 작동

-데이터 분산을 해싱알고리즘에 의해 오라클이 결정. 파티션 개수를 2의 제곡으로 설정을 결정

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

<ex>

create table 고객 (고객id varchar2(5), 고객명 varchar2(10),.....)

partition by hash (고객id) partition 4;

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

 

  • 병렬 쿼리 성능 향샹
  • DML 경합 분산

 

(4)리스트 파티션

-사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장

-단일 컬럼으로만 파티션 키를 지정할 수 있음

-maxvalue 대신 default 파티션을 생성.

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

<ex>

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) ) ;

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

 

(5)결합 파티션

-파티션 키에 따라 1차적으로 데이터를 분배하고 서브 파티션 키에 따라 최종적으로 저장할 위치(세그먼트)를 결정

 

[range + hash] 결합 파티션

create table 주문( 주문번호number, 주문일자varchar2(8), 고객id varchar2(5) )
partition by range(주문일자) 
subpartitionby hash(고객id) subpartitions 8
( 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 파티션 내에서 다시 해시 알고리즘을 사용해 각 서브 파티션으로 데이터를 분할 저장
 
select * from 주문 where 주문일자 between '20090701' and '20090930'
//P2009_q3에 속한 8개의 서브파티션만 탐색
 
select * from 주문 where 고객id= :custid
//각 range 파티션당 하나씩 총 6개의 서브파티션만 탐색
 
 
[range + list]결합 파티션
-판매일자 기준으로 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') );
 
(6)11g에 추가된 파티션 유형
 reference 파티션
 -부모 테이블 파티션 키를 이용해 자식 테이블을 파티셔닝하는 기능이 도입
Create table 상품 {
상품번호 number NOT 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);
-----------------------------------------------------------------
SQL> select table_name,partition_name from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
상품                           P1
상품                           P2
상품                           P3
상품                           P4
일별상품거래                   P1
일별상품거래                   P2
일별상품거래                   P3
일별상품거래                   P4
------------------------------------------------------------------
 
 
 
 
interval 파티셔닝
-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 ) 
) ;

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