[Oracle] 오라클 파티셔닝, Partitioning

정미나 2019. 5. 31. 17:09

테이블 파티셔닝

- 하나의 테이블을 파티션 키에 따라 물리적으로 별도의 세그먼트에 저장

- 파티셔닝의 이점

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

- 클러스터, IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속함

- 내부에 몇 개의 세그먼트를 생성하고 그것들이 논리적으로 하나의 오브젝트임을 메타 정보로 딕셔너리에 저장해 두는 것(테이블:세그먼트 = 1:M)

오라클 버전 8 이전에는 파티션 뷰를 통해 파티션 기능을 구현하는 수동 파티셔닝을 이용했는데 실제로 11버전을 사용하고 있는 지금 시스템에서도 이런 형태의 VIEW를 본적이 있다.

테이블명_201901
테이블명_201902
테이블명_201903
테이블명_201904

위와 같이 월별로 테이블을 생성해서 데이터를 쌓은 후 (체크 제약 필수!)

CREATE OR REPLACE VIEW PARTITION_VIEW
AS
SELECT * FROM 테이블명_201901
UNION ALL
SELECT * FROM 테이블명_201902
UNION ALL
SELECT * FROM 테이블명_201903
UNION ALL
SELECT * FROM 테이블명_201904 ;

위와 같이 UNION ALL로 묶어 파티션 뷰를 생성하는 것이다.
하지만 이런 형태의 VIEW는 바인드 변수를 사용할 때 10g나 11g에서 파티션 Pruning이 제대로 작동하지 않아 VIEW를 이용하여 쿼리를 짤 경우 모든 테이블을 access하는 비효율이 발생하게 된다.

Range 파티셔닝

- 주로 날짜 컬럼을 기준으로 파티셔닝
- 이력성 데이터 조회 시 성능이 크게 향상됨
- 파티션 키 컬럼 : 1~16개 까지 가능

CREATE TABLE ORD_RANGE (
ORD_NO NUMER(10) NOT NULL
      , ORD_DT VARCHAR2(8) NOT NULL
      , ORD_HMS VARCHAR2(6)
      , BRANCH_CD VARCHAR2(10)
      , ...
)
PARTITION BY RANGE(ORD_DT) (
PARTITION P201901 VALUES LESS THAN('201902')
      , PARTITION P201902 VALUES LESS THAN('201903')
      , PARTITION P201903 VALUES LESS THAN('201904')
      , PARTITION P201904 VALUES LESS THAN('201905')

      , PARTITION P_DEFAULT VALUES LESS THAN(MAXVALUE)
);

32회 SQLP 시험에 VALUES LESS THAN이 단답형으로 나왔었다!!

처음 문제를 풀때는 아무 생각없이 VALUE LESS THAN이라고 적어놨었는데

다 풀고 검토할 때 불현듯 VALUES라는 단어가 머릿속에 뙇 떠올라서 기적적으로 맞았다는 후문!! ㅋㅋ

몇 회인지는 기억이 안나지만 그 전에는 MAXVALUE가 단답형으로 나왔던 적도 있다! 그 땐 틀렸었... ㅜㅜ 또르르..

Hash 파티셔닝

- 파티션 키에 hash 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장
- 고객ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준 컬럼으로 선정해야 효과적
- hash 알고리즘 특성상 equal 조건이나 IN-List 조건 검색 시에만 파티션 Pruning 작동
- 특정 파티션에 데이터가 몰리지 않도록 하려면 파티션의 개수를 2의 제곱으로 설정할 것을 권고

CREATE TABLE CUST_HASH(
CUST_NO VARCHAR(10) NOT NULL
      , CUST_ID VARCHAR(30)
      , CUST_NAME VARCHAR(50)
      , ...
)
PARTITION BY HASH (CUST_NO) PARTITIOS 8;

List 파티셔닝

- 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장
- 단일 컬럼으로만 파티션 키 지정 가능

CREATE TABLE PRD_LIST(
PRODUCT_CD VARCHAR2(10)
      , PRODUCT_NM VARCHAR2(100)
      , LOCAL VARCHAR2(20)
      , ...
)
PARTITION BY LIST(LOCAL)
(
PARTITION P_지역1 VALUES ('서울')
      , PARTITION P_지역2 VALUES ('경기', '인천')
      , PARTITION P_지역3 VALUES ('부산', '대구', '대전', '광주')
      , PARTITION P_기타 VALUES (DEFAULT) -> 기타 지역
);

결합(복합) 파티셔닝

- 서브 파티션마다 세그먼트를 하나씩 할당하고, 서브 파티션 단위로 데이터를 저장
- 주 파팉션 키에 따라 1차적으로 데이터를 분배, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정
- 메인 파티션 : Range와 List만 가능
- 서브 파티션 : 모든 방식 가능
- 보관 기준과 처리 기준이 다를 때 사용하면 유용
ex) Range(삭제기준컬럼) - Hash(조인기준컬럼)

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