파티션이란
파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 파티션 단위로 나누어 저장하는 것을 말한다.
테이블과 인덱스 데이터를 파티셔닝하면 파티션 키에 따라 물리적으로 별도의 세그먼트에 데이터를 저장한다.
파티셔닝이 필요한 이유는 다음과 같다.
- 관리적 측면: 파티션 단위 백업/추가/삭제/변경
- 성능적 측면: 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산
대용량 테이블을 파티셔닝하면 보관주기가 지난 데이터를 별도 장치에 백업하고 지우는 일과 인덱스를 재생성하는 일을 효율적으로 할 수 있다.
또한 대용량 테이블을 Full scan하는 것도 파티셔닝을 하면 파티션 단위(물리적으로 세그먼트 단위)로 할 수 있다.
Oracle이 지원하는 파티션 유형은 다음과 같다.
Range 파티셔닝
파티션 키 값의 범위(Range)로 분할
파티셔닝의 가장 일반적인 형태이며 주로 날짜 칼럼을 기준으로 한다.
예를 들어 판매 데이터를 월 별로 파티션을 나눌 수 있다.
Hash 파티셔닝
파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션을 매핑한다.
데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리한다. 그렇기 때문에 각 로우가 어떤 파티션에 저장될 지 예측이 안 된다.
고객번호나 주문일련번호같이 파티션 키의 데이터 분포가 고른 칼럼이어야 효과적이다.
병렬처리와 DML 경합 분산에 좋다.
List 파티셔닝
불연속적인 값의 목록을 각 파티션에 지정한다.
순서와 상관 없이, 사용자가 미리 정한 그루핑 기준에 따라 데이터를 분할 저장한다.
예를 들어 판매 데이터를 지역별로 분할할 수 있다.
Composite 파티셔닝
Range나 List 내에 또 다른 서브 파티션을 구성한다.
예를 들어 Range + List를 사용하거나 List + Hash 로 파티셔닝을 할 수 있다.
두 이점을 합칠 수 있다.
오라클에서 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 ) → 주문일자 >= '20100401' ) ;Range + Hash 로 생성하려면 다음과 같다.
create table 주문 (
주문번호 number, 주문일자 varchar2(8), 고객id varchar2(5),
) partition by range(주문일자) subpartition by 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 )
) ;파티션 Pruning
prune
동사 [VN](~ sth (back))
- 타동사
(나무를) 전지하다, (가지를) 잘라 내다[치다]- 타동사
(부분들을 없애) 축소하다, (불필요한 부분을) 가지치기하다[쳐내다]
파티션 pruning은 옵티마이저가 불필요한 파티션을 액세스 대상에서 제외하는 기능을 말한다.
이를 통해 액세스 조건과 관련된 파티션에서만 작업을 수행할 수 있게 된다.
파티션 테이블에서 조회나 DML을 수행할 때 극적인 성능을 개선하는 핵심 원리가 바로 이것이다.
기본 파티션 pruning에는 정적, 동적 pruning이 있고 서브쿼리 pruning, 조인 필터 pruning 같은 고급 pruning 기법도 존재한다.
정적 파티션 pruning
액세스할 파티션을 컴파일 시점에 미리 결정한다.
파티션 키 칼럼을 상수 조건으로 조회하는 경우에 작동한다.
select *
from sales_range
where 1=1
and sales_date >= '20060301'
and sales_date <= '20060401'위 쿼리는 sales_range 테이블이 sales_date 기준으로 range 파티셔닝 된 테이블에서 사용 가능하다.
동적 파티션 pruning
컴파일 시점이 아닌 실행시점에 결정한다. 주로 파티션 키 칼럼을 바인드 변수로 조회하는 경우가 대표적이다.
NL Join할 때도 inner 테이블이 조인 칼럼 기준으로 파티셔닝 돼 있으면 동적 pruning이 작동한다.
select *
from sales_range
where
sales_date >= :a and
sales_date <= :b인덱스 칼럼 가공하면 인덱스 사용 못하는 것이랑 같은 이치로, 파티션 키를 가공하면 역시 파티션 pruning이 정상적으로 일어나지 않는다.
인덱스 파티셔닝
위에는 테이블 파티셔닝에 관한 내용이었다. 인덱스 파티셔닝은 테이블 파티션과 구분되어야 한다.
Local vs Global 파티션 인덱스
Local 파티션 인덱스
인덱스 파티션 키를 사용자가 따로 지정하지 않고, 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리해준다.
인덱스 데이터가 파티션마다 따로 있다.
Global 파티션 인덱스
테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스이다.
Prefixed vs Nonprefixed 파티션 인덱스
인덱스 파티션 키 칼럼이 인덱스 구성 상 왼쪽 선두 칼럼에 위치하는지에 따른 구분이다.
prefixed: 파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 왼쪽 선두에 둔다.
nonprefixed: 왼쪽 선두에 두지 않거나, 파티션 키가 인덱스 칼럼에 속하지 않는다.
local이냐 global이냐, prefixed냐 nonprefixed냐에 따라서 인덱스 파티션 방법은 4가지로 나뉜다.
오라클은 global nonprefixed, 인덱스 파티션이 테이블 파티션과 독립적으로 존재하면서 파티션 키가 인덱스 칼럼의 선두가 아니거나 칼럼에 포함되지 않는 구성을 허용하지 않는다.
인덱스 파티셔닝 가이드
| 구분 | 적용기준 및 고려사항 |
|---|---|
| 비파티션(파티션 안함) | - 파티션 키 칼럼이 조건절에 누락되는 쿼리 사용이 잦을 때 - NL Join에서, inner 테이블의 파티션 키 조건이 범위가 넓어져서 결국 봐야 하는 파티션이 많아지는 경우가 잦을 때 - 파티션 인덱스를 이용하면 sort order by 대체 효과를 상실한다. 소트 연산을 대체해서 부분범위처리를 활용하고자 할 때 - 테이블 파티션 이동, 삭제 등의 작업 시 Unusable 되므로 적용 시 주의해야 함 |
| Global Prefixed | - 인덱스 경합 분산에 효과적 - 여러 Local 인덱스 파티션을 액세스하는 것이 비효율적일 때 - 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의해야 함 |
| Local Prefixed | - 관리적 측면에서 유용: 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때 - 이력성 데이터를 주로 관리하는 DB 환경에 효과적 - 파티션 키 칼럼이 '=' 조건으로 사용될 때 유용 - 파티셔닝 칼럼에 대한 검색 조건이 없으면 인덱스 선두 칼럼이 조건절에 누락된 것이므로 정상적인 사용이 불가(index full scan은 가능) - 파티션 키 칼럼(= 인덱스 선두 칼럼)이 범위 검색 조건일 때 불리 |
| Local Nonprefixed | - 관리적 측면에서 유용: 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때 - 이력성 데이터를 주로 관리하는 DB 환경에 효과적 - 파티션 키 칼럼이 조건절에 사용될 때 유용 - 파티셔닝 칼럼에 대한 검색 조건이 없으면 인덱스 파티션 전체를 액세스하는 비효율이 발생할 수 있으므로 주의 - 파티션 키 칼럼이 범위검색 조건으로 자주 사용된다면 local prefixed보다 local nonprefixed가 유리. 그렇더라도 좁은 범위검색이어야 함 |
