SQL) 파티셔닝 Ⅰ

jinsung·2026년 1월 15일
SQLPoraclesql데이터자격증

SQL

목록 보기
41/46
post-thumbnail

1. 테이블 파티셔닝

파티셔닝은 테이블과 인덱스 데이터를 파티션단위로 나누어 저장하는 것을 말한다.
테이블을 파티셔닝하면 하나의 테이블일지라도 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터가 저장되며, 인덱스도 마찬가지다.

✅ 파티셔닝이 필요한 이유

  • 관리적 측면

    파티션 단위 백업, 추가, 삭제, 변경

  • 성능적 측면

    파티션 단위 조회 및 DML 수행

파티셔닝도 클러스터, IOT와 마찬가지로 관련 있는 데이터가 흩어지지 않고 물리적으로 인접하도록 저장하는 클러스터링 기술에 속한다.
클러스터와 다른 점은 세그먼트에 저장한다는 것이다.

1. 파티션 기본 구조

✅ 수동 파티셔닝

오라클 버전 8부터 파티션 테이블이 처음 제공됐다.
이전 버전에서 파티션 뷰를 통해 직접 파티션 기능을 구현했으며, 이를 '수동 파티셔닝' 이라고 부른다.

파티션 뷰는 Base 테이블 정의, 각 테이블 별 체크 제약 및 인덱스 생성, 통계수집 후 테이블들을 union all 연산을 통해 생성하면 된다.

파티션 뷰의 핵심 기능은 뷰 쿼리에 사용된 조건절에 부합하는 테이블만 읽어오는 것이고 이를 '파티션 Purning' 이라고 한다.

✅ 파티션 테이블

오라클 버전 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;

create index pt_empno_idx on partition_table(empno) LOCAL; -> 각 파티션별 개별적 인덱스

partiton by 절은 파티션 뷰의 Base 테이블에 체크 제약을 설정하는 것과 같은 역할을 한다.
파티션 테이블을 위처럼 정의하면, 세 개의 세그먼트가 생성되어 앞의 파티션 뷰와 구조적으로 같다.

이처럼 파티셔닝은, 내부에 몇 개의 세그먼트를 생성하고 그것들이 논리적으로 하나의 오브젝트임을 메타 정보로 딕셔너리에 저장해 두는 것이다.

파티션되지 않은 일반 테이블일때는 테이블과 세그먼트는 1:1 관계이지만, 파티션 테이블일 때는 1:M 관계이다. 인덱스를 파티셔닝할 때도 마찬가지다.

2. Range 파티셔닝

오라클 8 버전부터 제공된 가장 기초적인 파티셔닝 방식으로서, 주로 날짜 컬럼을 기준으로 한다.

create table order (ordernumber number, orderdate varchar2(8), customerid varchar(5), ...)

partition by range(orderdate) (
	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_q5 values less than ('20100401'),
    partition p9999_max values less than (MAXVAULE) -- orderdate > '20100401'
);

위 쿼리처럼 파티셔닝 테이블에 값을 입력하면 각 레코드를 파티션 키 컬럼 값에 따라 분할 저장하고, 읽을 때도 검색 조건을 만족하는 파티션만 읽을 수 있어 이력성 데이터 조회 시 성능이 크게 향상된다.

파티션 키는 하나 이상의 컬럼을 지정할 수 있고, 최대 16개까지 허용된다.

과거 데이터가 저장된 파티션만 백업하고 삭제하는 등 데이터 관리 작업을 효율적이고 빠르게 수행할 수 있는 큰 장점이 있다.

11g부터는 Range 파티션을 생성할 때 interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.

3. 해시 파티셔닝

오라클 8i부터 Range 파티셔닝에 이어 해시파티셔닝을 제공한다.

파티션 키에 해시 함수를 적용한 결과 값이 같은 레코드를 같은 파티션 세그먼트에 저장해 두는 방식이며, 주로 고객 ID처럼 변별력이 좋고 데이터 분포가 고른 컬럼을 파티션 기준으로 선정해야 효과적이다.

검색할 때는 조건절 비교값에 해시 함수를 적용해 읽어야 할 파티션을 결정하며, 해시 알고리즘 특성상 등치조건 또는 IN-List 조건으로 검색할 때만 파티션 Pruning이 작동한다.

create table 고객 (고객id varchar2(5), 고객명 varchar2(10), ...)
partition by hash(고객id) partitions 4;

해시 파티셔닝의 테이블 파티셔닝 여부를 결정할 때는 데이터가 얼마나 고르게 분산될 수 있느냐가 가장 중요한 포인트이다.
해시 파티셔닝할 때 특히 데이터 분포를 신중히 고려해야 하는데, 사용자가 직접 파티션 기준을 정하는 Range과 리스트 파티셔닝과 다르게 해시 파티셔닝은 파티션 개수만 사용자가 결정하고 데이터를 분산시키는 해싱 알고리즘은 오라클에 의해 결정되기 때문이다.

오라클은, 특정 파티션에 데이터가 몰리지 않도록 하려면 파티션 개수를 2의 제곱으로 설정할 것을 권고한다.
이 규칙을 따르더라도 파티션 키 컬럼의 Distinct Value 개수가 적다면 데이터가 고르게 분산되지 않을 가능성이 높으므로, 이때는 리스트 파티션을 이용해 파티션 기준을 사용자가 수동으로 결정해 주는 것이 좋다.

✅ 병렬 쿼리 성능 향상

데이터가 모든 파티션에 고르게 분산돼 있거나, 각 파티션이 서로 다른 디바이스에 저장돼 있을 때 해시파티셔닝을 사용하면 병렬 I/O 성능을 극대화 시킬 수 있다.
반대로 말하면, 데이터가 고르게 분산되지 않을 때, 병렬 쿼리 효과는 반감된다.

✅ DML 경합 분산

병렬 쿼리 성능 향상뿐 아니라 동시 입력이 많은 대용량 테이블이나 인덱스에 발생하는 경합을 줄일 목적으로도 해시 파티셔닝을 사용한다.
대용량 거래 테이블일수록 DML 발생량이 많아 경합 발생 가능성도 그만큼 크다.

Right Growing 인덱스도 맨 우측 끝 블록에만 값이 입력되기 때문에 자주 경합지점이 되곤 하는데, 이때도 인덱스를 해시 파티셔닝함으로써 경합 발생 가능성을 줄일 수 있다.

위의 병렬 쿼리 성능 향상이나 DML 경합 분산 이 두 가지 모두 트랜잭션이 많이 발생하는 대용량 거래 테이블일 때야 효과가 있다.
단일 해시 파티셔닝보다는 [ Range + 해시 ]를 조합한 결합 파티셔닝을 주로 사용하는 이유이다.

4. 리스트 파티셔닝

오라클 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)
);

Range 파티션에선 값의 순서에 따라 저장할 파티션이 결정되지만, 리스트 파티션에서는 순서와 상관없이 불연속적인 값의 목록으로써 결정된다.

해시 파티션과 비교하면, 해시 파티션은 오라클이 정한 해시 알고리즘을 사용하지만, 리스트 파티션은 사용자가 정의한 논리적인 그룹에 따라 분할한다.

리스트 파티셔닝은 오직 단일 컬럼으로만 파티션 키를 지정할 수 있다.
그리고 dafault 파티션을 생성해 두어야 한다.

5. 결합 파티셔닝

결합 파티셔닝을 구성하면 서브 파티션마다 세그먼트를 하나씩 할당하고, 서브 파티션 단위로 데이터를 저장한다.

즉, 주 파티션 키에 따라 1차적으로 데이터를 분배하고, 서브 파티션 키에 따라 최종적으로 저장할 위치를 결정한다.

8i에서는 [ Range + 해시 ] 형태만 가능했지만 9i부터는 [ Range + 리스트 ] 형태도 지원한다.
11g 부터는 주 파티션이 해시만 아니면 모든 조합을 지원한다.

✅ [ Range + 해시 ] 결합 파티셔닝

create table 주문 (주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5), ...)
partition by range(주문일자)
	partition by hash(고객id) subpartition 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_q5 values less than('20100401'),
      partition p9999_q1 values less than(MAXVAULE)
);

위 쿼리는 각 Range 파티션 내에서 다시 해시 알고리즘을 사용해 각 서브 파티션으로 데이터를 분할 저장한다.

select *
from 주문
where 주문일자 between '20090701' and '20090930'

이 쿼리로 주문 테이블을 탐색하면 Range 파티션 p2009_q3에 속한 8개의 서브 파티션을 탐색한다.

select *
from 주문
where 고객id = :custid

이 쿼리로 주문일자 조건없이 고객 id로만 조회하면, 각 Range 파티션당 하나씩 총 6개 서브 파티션을 탐색한다.

✅ [ Range + 리스트 ] 결합 파티셔닝

create table 판매 ( 판매점 varchar2(10), 판매일자 varchar2(8), ...)
partition by range(판매일자)
subpartition by list(판매점)
subpartition templete (
	subpartition 1st_01 values ('강남', '강북', '강서', '강동'),
    subpartition 1st_02 values ('부산', '대전'),
    subpartition 1st_03 values ('인천', '제주', '의정부'),
    subpartition 1st_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와 리스트 파티셔닝의 이점을 둘 다 누릴 수 있다.

이 결합 파티셔닝은 초대형 이력성 테이블을 Range 파티셔닝하고, 각 파티션을 업무적으로 다시 분할하고자 할 때 주로 사용한다.

✅ 기타 결합 파티셔닝

11g부터 네 가지 형태의 결합 파티셔닝 기능이 추가되었다.
따라서 주 파티션이 해시 파티셔닝만 아니라면 모든 조합이 가능해졌다.

  • Range - Range

  • 리스트 - 해시

  • 리스트 - 리스트

  • 리스트 - Range

6. 11g에 추가된 파티션 유형들

✅ Reference 파티셔닝

반정규화가 필요한 데이터 모델에서 부모 파티션 테이블 키를 이용해 자식 테이블을 파티셔닝하는 기능이 도입되었는데, 이를 'Reference 파티션' 이라고 부른다.
이 기능을 사용하려면 자식 테이블의 컬럼에 not null과 fk 제약이 있어야 한다.

✅ Interval 파티셔닝

Range 파티션을 생성할 때 interval 기준을 정의함으로써 정해진 간격으로 파티션이 자동 추가되도록 할 수 있다.
특히 테이블을 일 단위로 파티셔닝했을 때 유용하다.
inverval 수치만큼을 넘을 때마다 테이블에 파티션이 추가되도록 할 수 있다.

이 외에도 시스템 파티셔닝, 가상 컬럼 기반 파티셔닝 등이 11g에 추가되었다.

2. 파티션 Pruning

파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능이다.

파티션 테이블에 대한 쿼리나 DML을 수행할 때 극적인 성능 개선을 가져다주는 핵심 원리가 파티션 Pruning에 있다.

1. 기본 파티션 Pruning

  • 정적 파티션 Pruning

    파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 액세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 특징이다.
    실행계획의 Pstart와 Pstop 컬럼에는 액세스할 파티션 번호가 출력된다.

  • 동적 파티션 Pruning

    파티션 키 컬럼에 바인드 변수로 조회하면 쿼리 최적화 시점에는 액세스할 파티션을 미리 결정할 수 없다.
    실행 시점이 돼서야 사용자가 입력한 값에 따라 결정되며, 실행계획의 Pstart와 Pstop 컬럼에는 'KEY' 라고 표시된다.
    NL 조인 할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다.

파티션 컬럼에 IN-List 조건을 사용하면 상수 값이라도 Pstart,Pstop 컬럼에 KEY(I)가 표시된다.

파티션 키 컬럼을 함수를 가공하거나 타입을 안 맞춰줘서 묵시적 형변환이 일어나면 파티션 Pruning이 일어나지 않아, 파티션 키 컬럼도 함부로 가공해서는 안 된다.

✅ 동적 파티션 Purning 시 테이블 레벨 통계 사용

바인드 변수를 사용하면 최적화 시점에 파티션을 확정할 수 없어 동적 파티션 Pruning이 일어나서, 쿼리 최적화에 테이블 레벨 통계가 사용된다.
반면, 정적 파티션 Pruning 일 때는 파티션이 확정되기 때문에 파티션 레벨 통계가 사용된다.

테이블 레벨 통계는 파티션 레벨 통계보다 부정확하기 때문에 옵티마이저가 잘못된 실행계획을 수립하는 경우가 생기며, 이는 바인드 변수 때문에 생기는 대표적인 부작용 중 하나이다.

조인에 사용되는 고급 파티션 Pruning 기법으로는 두 가지가 있다.

2. 서브쿼리 Pruning (8i~)

서브쿼리 Pruning이 일어나면 액세스해야 할 파티션 번호 목록이 구해지며, 필요한 파티션만 스캔할 수 있다.
실행계획 상 Pstart, Pstop에는 KEY(SQ)가 나타나며 SQ는 SubQuery를 뜻한다.
이 방식으로 파티션 Pruning 을 하면 Outer 테이블을 한 번 더 읽게 되므로, 서브쿼리 Pruning 적용 여부는 옵티마이저가 비용을 고려해 내부적으로 결정한다.

제거될 것으로 예상되는 파티션 개수가 상당히 많고, where 조건절을 가진 Outer 테이블이 파티션 테이블에 비해 상당히 작을 때만 서브쿼리 Pruning이 작동한다.

3. 조인 필터(=블룸 필터) Pruning (11g~)

서브쿼리 Pruning은 Outer 테이블을 한 번 더 액세스하는 추가 비용이 발생하기 때문에, 오라클은 11g부터 블룸 필터 알고리즘을 기반으로 한 조인 필터 Pruning 방식을 도입했다.

조인 필터 Pruning의 기능은 파티션 테이블과 조인할 때, 읽지 않아도 되는 파티션을 제거해 주는 것이다.

이 기능을 적용하면 실행계획에 part join filter create와 partition range join-filter를 포함하는 두 개 오퍼레이션 단계가 나타난다.
블룸 필터를 생성해서 블룸 필터를 이용해 파티션 Pruning을 하는 것이다.

블룸 필터의 역할은, 교집합이 아닌 원소를 찾는 역할이다.
조인 필터 Pruning 에서도 조인 대상 집합을 포함하는 파티션을 찾는 게 아니라, 포함하지 않는 즉 읽지 않아도 되는 파티션을 찾는 것이다.

4. SQL 조건절 작성 시 주의사항

partition m09 values less than('20091001'),          -- 0901 ~ 0930
partition m10 values less than('20091101'),. ...      -- 1001 ~ 1031

select *
from 고객
where 가입일 like '200910%'

위 쿼리에서 m10 파티션만 스캔하지 않고 m09 파티션도 스캔한다.
왜냐하면 m09 파티션에 '200910~~', '20091000$' 이런 문자열 형태로의 데이터가 있을 수 있기 때문이다.

따라서 위와 같이 일자로써 파티션 키 값을 정의했다면 between 연산자를 이용해 정확한 값 범위를 주고 쿼리해야 한다.

select *
from 고객
where 가입일 between '20091001' and '20091031'

파티션 설계와 상관없이 옵티마이저가 효율적인 선택을 할 수 있도록 하려면 between 연산자를 사용해야 한다.
만일 일일이 SQL을 수정하기 곤란하다면 연월로써 파티션 키 값을 다시 정의해 주면 like 연산자를 쓸 수 있다.

profile
jinsung
Data Engineer
이전 포스트

SQL) 소트 튜닝 Ⅲ

다음 포스트

SQL) 파티셔닝 Ⅱ

0개의 댓글